How do I use Countif with two criteria?

Hello

I have a spreadsheet with two named ranges: Dates and City
I'd like to find in G3 all orders placed in the year 2012 in, for example, New York
I tried formulae with Countif but it didn't work. Any suggestions? I shouldn't use PT.

Thanks,

Massimo
Dates.JPG
Dates.xlsm
Massimo ScolaAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you cannot do that with Countif() and not with Countifs() either, since the data in column B has whole dates and you are querying on the year only. In this case you need good old Sumproduct:

=SUMPRODUCT(--(YEAR(Dates)=$G$1),--(City=$G$2))

cheers, teylyn
0
 
Saqib Husain, SyedEngineerCommented:
you can use countifs instead of countif
0
 
barry houdiniCommented:
You can use COUNTIFS if you take the year and use it to set the start and end dates, i.e.

=COUNTIFS(B:B,">="&DATE(G1,1,1),B:B,"<"&DATE(G1+1,1,1),C:C,G2)

regards, barry
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Never say never when dates are involved and barry is around :)
0
 
Rob HensonFinance AnalystCommented:
Not wishing to knock Barry off his extremely high pedestal, but looking at the sample data the dates aren't true dates anyway. I am going by the fullstop separator rather than a slash.

However, Barry's suggestion could no doubt be adapted to use RIGHT function to pull the year from the date string.

Thanks
Rob H
0
 
barry houdiniCommented:
Hello Rob,

I wondered about that too.....but if you open the file you'll see they are real dates. 09.11.2011 will be the default date format in some parts of Europe....

regards, barry
0
 
Rob HensonFinance AnalystCommented:
Hi Barry,

When you've finished with the ladder climbing back up the pedestal, can I borrow it to climb out of the hole into which I have dug myself.

Thanks
Rob H
0
 
Massimo ScolaAuthor Commented:
Hello Barry

You are right. The dates are in the correct format. We use the DD.MM.YYYY format in Switzerland.
I haven't tried it yet but will give you feedback ASAP

Thanks
Massimo
0
 
sdwalkerCommented:
This is one of my favorite threads.  Thanks guys (girls included)!!
0
 
Massimo ScolaAuthor Commented:
Hi guys

I'd like to say "thanks a lot" to all of you

have a nice weekend

massimo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.