sumproduct

[Guest]

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?

 

[Andy B]

Hi

Yes, you can count with SUMPRODUCT, however you can't use full columns as
ranges. Try:
=SUMPRODUCT((NE!$N$3:$N$174 >-500)*(NE!$N$3:$N$174 <-1)*NE!E3:E174 ="M
Tel"))
ALl ranges must also be the same size.

 

[C-Money]

I tried that and would get a value of 0 which isn't
possible, since I know on the other sheet there are M Tel
that are w/in that -1 to -500 range. Is there another way

 

[Andy B]

Well, I can't think of an easier way that using this formula! I can only
suggest you make sure that there are records that fit the criteria. Maybe
using 2 formulas would help you find where the problem is:

=SUMPRODUCT((NE!$N$3:$N$174 >-500)*(NE!E3:E174 ="M Tel"))
=SUMPRODUCT((NE!$N$3:$N$174 <-1)*(NE!E3:E174 ="M Tel"))

 

[C-Money]

Hey I figured it out. I think I had a screwed cell
reference in my formula. Thanks a lot for the help

 

[Andy B]

Thanks for the thanks!!

--
Andy.

Hey I figured it out. I think I had a screwed cell
reference in my formula. Thanks a lot for the help