Hi Bouskila,

Please provide a sample Excel file of your data and desired result so I can understand what you're working with.

Mynda

Hy Mynda,

I see the file load but it seems it is not...

I need to count the number of vendor and Items for each object and get the value only for those with an amount 0

5 columns and 10000 lines

Objet Vendor items Amount example
C-600-2-X1-02-03 1000004684 1000003030 1000.00 Objet Vendor item s Amount
C-600-2-X1-02-03 1000004684 1000003030 20000.00 C-600-2-X1-02-03 1 1 242000
C-600-2-X1-02-03 1000004684 1000003030 0.00
C-600-2-X1-02-03 1000004684 1000003030 50000.00
C-600-2-X1-02-03 1000004684 1000003030 3000.00
C-600-2-X1-02-03 1000004684 1000003030 120000.00
C-600-2-X1-02-03 1000004684 1000003030 0.00
C-600-2-X1-02-03 1000004684 1000003030 17000.00
C-600-2-X1-02-03 1000004684 1000001563 15000.00
C-600-2-X1-02-03 1000004684 1000003031 16000.00

thanks
Stephanie

Hi Stepahnie,

Thanks for sharing the file. I'm a bit confused as you say you want the value for those with an amount of zero. How would I be calculating the value? In your example you are summing the amount for all objects. I thought maybe you wanted to count the number of different items that had amounts > 0, but your example shows 1, when there are 3.

Mynda

Hi Mynda,

it is really an honor to talk directly with you, i am a big fan !

I want to count the quantity of the items linked to 1 object but just count as one if there are many duplicate, and have the total amount, when the amount is 0, i dont count it

attached the file with the example, i think i have attached it twice, sorry

thanks
Stephanie

Hi Stephanie,

Thanks for clarifying. You can use a PivotTable (with data added to the data model) to do this. You need to include the 'Items' field in both the row labels and the values area. In the row lables it's filtered for sales amounts > 0, so the field must remain in the row lables for the filter to be effective.

You can then do a distinct count of the items in the values area.

See file attached. I've collapsed the objects group so the individual items don't display, but the column must still remain in the PivotTable. You can hide it if you prefer.

Mynda

Hi Mynda

Many thanks, I did already the pivot approach was more interested to know if we could manage by Excel formula ?

You can then do a distinct count of the items in the values area : how did you create this value for the field ?

thanks
Stephanie

Oh, by the time we got the question and example clear I'd forgotten you didn't want to use a PivotTable, sorry.

You need Microsoft 365 or Excel 2021 for the formula approach below:

=COUNTA(UNIQUE(FILTER($B$2:$B$13,($A$2:$A$13=G3)*($D$2:$D$130))))

Mynda

Hi Mynda,

thanks.

Could you check the formula, it doesnt seems to work

Stephanie

Hi Stephanie,

I don't see where you've inserted it in the file you attached. Note: if you don't have Excel 2021 or Microsoft 365, then the formula won't work as you won't have the required UNIQUE and FILTER functions. In which case you should use the PivotTable solution.

Mynda

Hi Myanda,

i added the formula column E,I was expected the same result as the Pivot ?
yes I used Microsoft l 365
thanks
Stephanie

If I may, perhaps the attached file does what you ask for. A formula solution that mimics the Pivot Table.
I've shaded the area green where you had the "example result".

Hi Stephanie,

You said my formula wasn't working (which happens to be the same formula Riny suggested). I wanted you to show me your file where you'd used my formula and it was returning an error. The file you're sharing with me simply shows the desired result with a hard keyed answer. I wanted to help you troubleshoot where the formula was going wrong, and I can only do that if you show me the formula you tried.

Mynda

Hi Mynda,

Actually there is no error in the formula but it doesnt show the result expected which is to count for one object the number of item without duplicate and to return the sum.
I added in the 2nd files the 2 columns of the formula in orange and also the pivot result.

thanks
Stephanie

Hi Stephanie,

That's for sharing the second file. That's the one that would have been helpful from the outset.

Sorry, I thought you were having trouble with the COUNT result, and that's what I gave you a formula for initially. For the SUM you can use SUMIFS like so:

=SUMIFS([[Total ]],[Object],[@Object],[Ref. document number],[@[Ref. document number]])

Mynda