Distinct COUNTIF

[TheNewUnion]

Hi

Apologies, can't upload from work, so I will do my best to explain what I am trying to do (and hope for the best!)

I have 2 x Columns of data, Column A has account number and Column B has item purchased (there are only 4-5 different items).

I need to summarise this info. So, summarising the items purchased is easy, but I also need to summarise purchases by distinct account. So, for example, look at a product in Column B, we've sold a totol of e.g 700 of them, but I need to know how many unique account numbers have purchased that item, so I might have 700 purchases, but 350 account numbers have bought 2 each. So I'd need my "distinct count" to show 350 there.

I have my list of the 4-5 items available for purchase in a table in a different sheet tab.

Does that make sense?

Thanks

[:) Sixthsense :)]

Use countifs or pivot table

[dilipandey]

Hi theNewUnion

Try pivot table here.. move account number in row field and item purchased in data field as sum / count as per your need AND you'll have unique account numbers in row field anywayas

regards,
DILIPandey

<click on below 'star' if this helps>

[TheNewUnion]

Yeah, just done a pivot table for it now and it works fine. Had been trying to work out a formula to do it, just for kicks, but I couldn't come up with anything. Would still be interested if anyone can do it with a formula!

Cheers guys

TNU

[Andrew-R]

I stuck a list of account numbers in cells A1:A8 and then used this formula:

=SUMPRODUCT(--(ROW(A1:A8)=MATCH(A1:A8,A1:A8,0)))

Edited to add: If your column has a header row then the formula becomes:

=SUMPRODUCT(--(ROW(A2:A8)=MATCH(A2:A8,A1:A8,0)))

Two of the range references exclude the header row, but the final reference must include it.

[TheNewUnion]

Superb, I will try it now.

(what's the deal with the -- seen it in a few formula on here but never really understood its purpose)

Thanks

TNU

Edit: Looks like a distinct count on a specific column, is there a wayof adapting so it only applies the count if a certain criteria in Clumn B is met, kind of like a COUNTIF wrapped around the Distinct formual)

Cheers

[Andrew-R]

The --(Statement) forces a TRUE/FALSE statement to convert to 1/0, which is handy if you want to sum them inside a SUMPRODUCT.

So, my formula gets the row for each cell in the range A1:A8 (i.e. it returns {1,2,3,4,5,6,7,8}) and then looks up each cell value in the same range. If the value hasn't previously appeared then the MATCH will return the same number as the row and the statement will return TRUE, if the value has previously appeared then the MATCH will return a lower value than ROW and the statement will return FALSE.

The -- then converts TRUE to 1 and FALSE to 0 and adds them up, which gives a count of the number of unique entries.

[TheNewUnion]

Excellent explanation Andrew-R, makes sense really but it never clicked before. Thanks for the response.