I need my formula to look at numerical and text data, can it do that? Eg
COUNTIF(C2:C50,"1",and D2:D50,"M") I know COUNTIF won't work, but how do I
phrase the formula? I have a ton of these to work out for a mega spreadsheet
and I am stuck!! All help appreciated!

Hi,
you can but what is the result you are looking for once you met both
condition summarize another column??

"Nightrunning" wrote:

I need my formula to look at numerical and text data, can it do that? Eg
COUNTIF(C2:C50,"1",and D2:D50,"M") I know COUNTIF won't work, but how do I
phrase the formula? I have a ton of these to work out for a mega spreadsheet
and I am stuck!! All help appreciated!

=SUMPRODUCT(--(C2:C5=1),--(D2:D50="M"))

Will do a countif both columns meet the criteria.

"Eduardo" wrote:

Hi,
you can but what is the result you are looking for once you met both
condition summarize another column??

"Nightrunning" wrote:

I need my formula to look at numerical and text data, can it do that? Eg
COUNTIF(C2:C50,"1",and D2:D50,"M") I know COUNTIF won't work, but how do I
phrase the formula? I have a ton of these to work out for a mega spreadsheet
and I am stuck!! All help appreciated!

If that 1 is a really a number:
=sumproduct(--(c2:c50=1),--(d2:d50="m"))

or

If that 1 is really text:
=sumproduct(--(c2:c50="1"),--(d2:d50="m"))

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

=====
And if you're running xl2007, there's a new =countifs() function. You can read
about that in excel's help.

Nightrunning wrote:

I need my formula to look at numerical and text data, can it do that? Eg
COUNTIF(C2:C50,"1",and D2:D50,"M") I know COUNTIF won't work, but how do I
phrase the formula? I have a ton of these to work out for a mega spreadsheet
and I am stuck!! All help appreciated!

--

Dave Peterson

Since you have a ton of these, this link might be helpful...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Nightrunning" wrote:

I need my formula to look at numerical and text data, can it do that? Eg
COUNTIF(C2:C50,"1",and D2:D50,"M") I know COUNTIF won't work, but how do I
phrase the formula? I have a ton of these to work out for a mega spreadsheet
and I am stuck!! All help appreciated!