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

1. ## 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

2. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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

3. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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

4. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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

5. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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.

6. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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.

7. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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.

8. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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.

9. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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?

10. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

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...

11. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

Good deal. Thanks for the feedback!

12. ## Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

If you're referring to email notifications then you only get notified of the first new post since your last visit.

On the forum, User CP, you only see the latest post at the time you refreshed the page, and it will only be shown in bold if the post was submitted after you last viewed the thread.

It is down to you to check if you have had one reply, or several.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1