Excel count unique with a condition

Question

I have the below table and need to find a formula to count the unique instances in column B in relation to the months in column A:

MonthBooked Concatenation
Jun-14  JSM010414BookShopInc.RenewalBooks
Jun-14  JSM010414BookShopInc.NewBooksNew(Location)Mall
Jun-14  JSM010414BookShopInc.NewBooksNew(Location)Mall
Jun-14  JSM010414BookShopInc.NewBooksNew(Location)HighStreet
Dec-14  BCO121214BookShopInc.NewMagazinesBrandNew
Dec-14  BCO311214BookShopInc.NewPampletBrandNew
Jun-15  JSM010415BookShopInc.RenewalBooks
Dec-15  BookShopInc.Magazines
Dec-15  BookShopInc.Pamplet

I've seen similar questions on this fine website but nothing that either provides the solution or something that I can adapt to get there.

I've tried

{=SUM(IF($A$2:$A$10=A16,1/COUNTIF($B$2:$B$10,$B$2:$B$10)))}

(where A16 = a cell populated with 'Jun-14') but it doesn't react correctly to changes in the data. Am I on the right lines here or is a different approach required?

Thanks in advance!

Al

What is the desired result? Your formula results in 3 with your data. Why is that not right? — Axel Richter
– Axel Richter, Commented Dec 12, 2014 at 13:35
Although the result is correct for the example given the quoted formula doesn't work correctly if any of the column B texts are repeated in another month as well as the month of interest, e.g. if you change the 2nd date down (A3) to Jul-14 the result is now 2.5 which obviously isn't correct - my suggested Answer should work in all circumstances — barry houdini
– barry houdini, Commented Dec 12, 2014 at 13:55

barry houdini · Accepted Answer · 2014-12-12 13:42:43Z

1

For consistent results use this approach

=SUM(IF(FREQUENCY(IF($A$2:$A$10=A16,IF($B$2:$B$10<>"",MATCH($B$2:$B$10,$B$2:$B$10,0))),ROW($B$2:$B$10)-ROW($B$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

answered Dec 12, 2014 at 13:42

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel count unique with a condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related