I need my formula to analyse numerical and text data
 

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!

I need my formula to analyse numerical and text data
 

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!

I need my formula to analyse numerical and text data
 

=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!

I need my formula to analyse numerical and text data
 

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

I need my formula to analyse numerical and text data
 

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!