counting multiple cells with multiple criteria

[cgisler]

I need the total number of cells that meet a specific criteria. I can break down what I need into 3 parts and using COUNTIF, get the results for each part. HOwever, I need the 3 parts to become one formula.

=COUNTIF(January!B:B, "OS")
=COUNTIF(January!A:A,">=1")
=COUNTIF(January!A:A, "<=7")

What I'm trying to do is create one formula that totals the number of cells that meet these specific criteria. It needs to 1) the cells in A:A need to be between a specific number range (technically I need it between a date range like 1/1/2011 and 1/7/2011) so that anything that falls on or between that range is counted. AND 2)the cells in B:B also match a specific criteria, "OS"

Is this even possible? and can someone help me create this formula?

 

[MrKowz]

Welcome to the forums!

You can do this in the following manner:

Excel 2003 or older:

=SUMPRODUCT(--(January!B1:B100="OS"),--(January!A1:A100>=Date(2011,1,1)),--(January!A1:A100<=Date(2011,1,7)))

Excel 2007 or newer:

=COUNTIFS(January!B:B,"OS",January!A:A,">="&Date(2011,1,1),January!A:A,"<="&Date(2011,1,7))

If you want to test it for the dates, then

 

[VoG]

=SUMPRODUCT(--(January!B1:B100="OS"),--(January!A1:A100,>=1),--(January!A1:A100 <=7))

 

[cgisler]

THanks!

I have excel 2003. It works perfectly!