I'm trying to count data based on the value in 2 columns. I'm using the
following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving
me the wrong count. Is there a way I can make this work?

Thanks,

=sumproduct(--(b2:b57="dla"),--(d2:d57=1))

Adjust the ranges to match--but you can't use whole columns.

=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

Marilyn wrote:

I'm trying to count data based on the value in 2 columns. I'm using the
following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving
me the wrong count. Is there a way I can make this work?

Thanks,

--

Dave Peterson

Hi!

Try this:

=SUMPRODUCT(--(B2:B57="DLA"),--(D2:D57=1))

Better to use cells to hold the criteria:

A1 = DLA
A2 = 1

=SUMPRODUCT(--(B2:B57=A1),--(D2:D57=A2))

Biff

"Marilyn" wrote in message
...
I'm trying to count data based on the value in 2 columns. I'm using the
following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is
giving
me the wrong count. Is there a way I can make this work?

Thanks,

SumProduct works great!!

Thank you ALL!!

"Marilyn" wrote:

I'm trying to count data based on the value in 2 columns. I'm using the
following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving
me the wrong count. Is there a way I can make this work?

Thanks,