sumproduct

[C-Money]

Is it possible to use SUMPRODUCT as a counting formula
when you have numeric and text criteria? For instance in
the table that I've created under the 1-500 section that
I've created I used this formula:

=SUMPRODUCT((NE!$N$3:$N$174 >-500)*(NE!$N$3:$N$174 <-1))

The data on the sheet that I'm referencing to has the E
column titled "Field/M Tel" In this column there is
either a blank cell or "M Tel" entered. I want to now
make a table that counts just the M Tel fields that fall
within the -1 to -500 section. I tried =SUMPRODUCT((NE!
$N$3:$N$174 >-500)*(NE!$N$3:$N$174 <-1)*NE!E:E ="M Tel"))

When I would enter that I would get a #NUM! error. Am I
entering this wrong or do I need an entirely different
formula?

 

[JulieD]

Hi

try
=SUMPRODUCT((NE!$N$3:$N$174 >-500)*(NE!$N$3:$N$174 <-1)*(NE!$E$3:$E$174 ="M
Tel"))
in sumproduct (AFAIK) the ranges must be the same size

Cheers
JulieD

 

[C-Money]

I got it. Thanks

-----Original Message-----
Hi

try
=SUMPRODUCT((NE!$N$3:$N$174 >-500)*(NE!$N$3:$N$174 <-1)* (NE!$E$3:$E$174 ="M
Tel"))
in sumproduct (AFAIK) the ranges must be the same size

Cheers
JulieD





.