COUNTIF with multiple criteria

[WLMPilot]

I don't think this can be done with COUNTIF, but I need to count using two
criterias:

The two criterias are:
1) A8:A1000 > 1/1/08
2) G8:G1000 <> ""

Thanks for your help.

Les

 

[Dave Peterson]

=sumproduct(--(a1:a1000>date(2008,1,1)),--(g8:g1000<>""))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

======
xl2007 has a couple of new functions:
=countifs()
and
=sumifs()

 

[WLMPilot]

Nevermind, I found the error. A1 needed to be A8.

Thanks,
Les

 

[WLMPilot]

The formula you provided is in C5 which is formated to numbers (no decimal)
and I got a #Value error.

Les

 

[Dave Peterson]

Sorry about the typo!

Nevermind, I found the error. A1 needed to be A8.

Thanks,
Les

 

[Ashish Mathur]

Hi,

Please type 1/1/2008 in cell B1. Now use the following formula

=sumproduct((A8:A1000>B1)*(G8:G1000<>""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com