Count Unique Items in a Column Based on Unique Items in Two Other Columns

I'm using this array formula to count the number of Unique Items in Column B for each Unique Item in Column A

Please Login or Register  to view this content.

I need to expand this formula so that it now counts the number of unique Colours in column C for each unique Animal in column B with a unique letter in column A. So when the entries are changed in cells J14 and J15 it gives the answer in cell J16. Please see sample attached.

Many thanks

Seems to me this is far simpler with a Pivot table and avoids all the messy stuff associated with complex formulae - see attached

Hi Richard,

It would be, but I can't use a Pivot Table for what I need to achieve which is why I'm tying to find a formulaic solution.

Many thanks

But if I could use a Pivot Table to identify the count then the next problem I have is then being able to populate different cells with the results, so for example, based on the pivot table we know there are 3 colours for Cat with the Letter B, what I then need to be able to do is for specific cells to be populated with those colours, e.g. F4 with the first colour, F5 with the second colour and F6 with the third colour and so on, bearing in mind there could be any number of unique colours that meet the criteria.

Many thanks

Not sure what results you expect.

See if this does what you want.

Array entered**:

=SUM(IF(FREQUENCY(IF((A2:A27=J14)*(B2:B27=J15),MATCH(C2:C27,C2:C27,0)),ROW(C2:C27)-ROW(C2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Try this array formula.

=SUM(IFERROR(1/COUNTIFS(A2:A27,J14,B2:B27,J15,Colour,Colour),0))

Although I think it only works because all of the colours are unique, if you have the same colour for several animals I think results could be wrong.

Hi,

The point about a pivot table is that the view of the data can be simply changed by dragging and dropping field names around. If you need colours reported in the table for a particular letter and animal then move those two fields into the Report Filter area of the PT - see attached for an example.

OK the colours may not be in F4, F5 & F6 but they are certainly in F5, F6 & F7 with the total of 3 in F8. But if you just want the total 3 then just drag the colours field into the Report Filter area.

Hi Richard,

I understand what you are saying and I think you are right, I can probably achieve what I need by adding the pivot to a hidden sheet and pulling the values from there.

Many thanks for your help, I think I was trying to over complicte this, though I'm curious to see if it can be achieved with a formula alone but for now I'll adopt the PT route I think.

Originally Posted by HangMan

I'm curious to see if it can be achieved with a formula alone

Did you try either of the formulas that were suggested?

Hi Tony and Jason,

Apologies, sometimes the forum behaves strangely, I didn't receive any notification of either of your repies, only Richards, so subsequently I didn't see either of your replies until now.

Many thanks, both solutions work perfectly and overcome my issue with not wanting to use a pivot table...

Very much appreciated, thank you...

Good deal. Thanks for the feedback!