CODEWITHSUNDEEP
excelexcel-formula
AlanP
AlanP

Reputation: 1

Excel count unique with a condition

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

Upvotes: 0

Views: 74

Answers (1)

barry houdini
barry houdini

Reputation: 46401

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

Upvotes: 1

Related Questions