How can I get a count when two columns meet certain criteria 1

[Bill4tektips]

I need to get a count when two different columns meet certain criteria. I have a column for Country and a column for Date and I need a count of what has been raised in France in January say to populate a Graph.
Can anyone help please?

 

[GlennUK]

How about:

=SUMPRODUCT((myCountryRange="France")*(myDateRange>=DateValue("1/Jan/2007"))*(myDateRange<=DateValue("31/Jan/2007")))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Bill4tektips]

GlennUK
Excel tells me that the formula contains an error and I can't see what it is.
What I have got is:
=SUMPRODUCT((Change_History!A2:A20,"UK")*(Change_History!G2:G20>=DATEVALUE("01/01/2007"))*(Change_History!G2:G20<=DATEVALUE("31/01/2007")))
as I am constructing the graphs on a different worksheet. I have changed the date format but that is all.
Any ideas?

 

[Bill4tektips]

I've solved it, I missed out the = in the Country Code.
Thanks for that

 

[GlennUK]

Great :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GhostWolf]

Thanks for this thread, guys. I got myself a good headache trying to find a variation of COUNTIF to do this.

Still don't understand why it's a SUMPRODUCT function, but it works!

 

[yogia]

Hi GhostWolf:

In addition to the fine contribution from Glenn, you may also want to try the following array formula ...

=COUNT(IF(countryRange="France",IF(MONTH(DateRange)=1,DateRange)))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[GhostWolf]

Thanks Yogia. I'll give that one a try when I get back to work Monday. That looks like a much more sensible approach to my task.