# 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
Asked:
###### Who is Participating?

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

EngineerCommented:
you can use countifs instead of countif
0

Commented:
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

Microsoft MVP ExcelCommented:
Never say never when dates are involved and barry is around :)
0

Finance 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

Commented:
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

Finance 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

Author 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

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

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

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.