COUNTIF with multiple criteria
 

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

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

Thanks for your help.

Les

COUNTIF with multiple criteria
 

=sumproduct(--(a1:a1000date(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 he
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 wrote:

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

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

Thanks for your help.

Les

--

Dave Peterson

COUNTIF with multiple criteria
 

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

Thanks,
Les

"Dave Peterson" wrote:

=sumproduct(--(a1:a1000date(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 he
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 wrote:

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

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

Thanks for your help.

Les

--

Dave Peterson

COUNTIF with multiple criteria
 

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

Les



"Dave Peterson" wrote:

=sumproduct(--(a1:a1000date(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 he
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 wrote:

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

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

Thanks for your help.

Les

--

Dave Peterson

COUNTIF with multiple criteria
 

Sorry about the typo!

WLMPilot wrote:

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

Thanks,
Les

"Dave Peterson" wrote:

=sumproduct(--(a1:a1000date(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 he
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 wrote:

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

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

Thanks for your help.

Les

--

Dave Peterson


--

Dave Peterson

COUNTIF with multiple criteria
 

Hi,

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

=sumproduct((A8:A1000B1)*(G8:G1000<""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"WLMPilot" wrote in message
...
I don't think this can be done with COUNTIF, but I need to count using two
criterias:

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

Thanks for your help.

Les