Need to Count number of unique rows for desired value

[peterszabla]

Hello,

I'm working on a spreadsheet and I have run into an issue that I have not been able to get around yet. I searched around the forum but have not found an answer so far. Any help would be appreciated.

The problem is as follows:

I have 3 columns. The first tracks the date, the second the country, and the third the expense that was incurred.


1/1/2013 Canada $45
1/1/2013 Canada $56
1/3/2013 Canada $15
1/3/2013 Uganda $23
1/3/2013 Uganda $25
1/5/2013 Canada $55
1/5/2013 Canada $45


For my purposes the expense column is not important, I just included it here to clarify the purpose of the spreadsheet. For each date there could be no entries, or multiple entries. I need to find out how many individual days exist for a country that I am searching for. So in the example above, the answer would be 3 if I searched for Canada, and 1 if I searched for Uganda.

I have attached a sample spreadsheet. I've been trying to find a solution using match, countifs and similar functions, but I can't come up with a formula that works. Any help would be greatly appreciated.

Peter

[FDibbins]

Hi

Try this...

I added a helper column in D and used this, copied down...
=IF(AND(COUNTIF($A$2:A2,A2)>1,B2=B1),"",1)

tthen used this to do the count...
=COUNTIFS($B$2:$B$14,"Canada",$D$2:$D$14,1)

Either put the counties in a list (prefered), or change 1 country to the next inside the formula

[icestationzbra]

ARRAY formula in G3:

Please Login or Register  to view this content.

or regular formula:

Please Login or Register  to view this content.

replace Canada with Portugal for G7.

does that help?

[peterszabla]

Thank you FDibbins, this worked great and was quite simple.

Peter