I need my formula to analyse numerical and text data

[Nightrunning]

I need my formula to look at numerical and text data, can it do that? Eg
COUNTIF(C2:C50,"1",and D250,"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!

 

[Eduardo]

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

 

[Sean Timmons]

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

Will do a countif both columns meet the criteria.

 

[Dave Peterson]

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 here:
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.

 

[Jim Thomlinson]

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

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