# SUMPRODUCT Problem

#### spectraflame

##### Well-known Member
I am using the following formula but I am not getting it to work correctly.

=SUMPRODUCT(--(EROUTES=B2),--(ETWACS>0))

The EROUTES name is a range that contains number from 10 to 670.

The ETWACS name is a range that contains the following formula.

=IF(ISERROR(VLOOKUP(H2,TWACSDATA,TRUE,FALSE)),"",VLOOKUP(H2,TWACSDATA,TRUE,FALSE))

What I am trying to do with the SUMPRODUCT formula is to count the number of "10's or 20's or 30's etc." that have a valid match from the VLOOKUP formula within the ETWACS range.

The SUMPRODUCT result that I am getting is 0. Is it because the cell is not turely blank because the VLOOKUP formula is present? Is there a better way to get the results that I am looking for?

Thanks,
Matthew

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try:

=SUMPRODUCT(--(EROUTES=B2),--(ETWACS+0>0))

Here is a sample of what I am trying to do.

The results should be:

10 = 1
20 = 2
30 = 0
Book2
ABCDEFGH
1METERFOUNDSOURCECOUNTCOUNTCOUNT
2ROUTESDBMATCHINTWACSFOR10FOR20FOR30
31010047753 222
410100477541004775410047754
520100477461004774610047746
620109020271090202710902027
730
830
Sheet1

Is there any reason why the formulas in F3,G3, & H3 are not good to use?
Book2
ABCDEFGH
1METERFOUNDSOURCECOUNTCOUNTCOUNT
2ROUTESDBMATCHINTWACSFOR10FOR20FOR30
31010047753 120
410100477541004775410047754
520100477461004774610047746
620109020271090202710902027
730
830
Sheet1

Why not change the formula in C3 to:

=ISNUMBER(MATCH(B3,\$D\$3:\$D\$8,0))+0

and change F1:H1 to COUNT FOR and F2:H2 to 10, 20, 30, etc.?

Invoke in F3:

=SUMIF(\$A\$3:\$A\$8,F\$2,\$C\$3:\$C\$8)

which you can copy across and down.

Once again you provide a solution that is very simple but very effective.

Matthew

Replies
12
Views
249
Replies
5
Views
209
Replies
1
Views
64
Replies
5
Views
485
Replies
5
Views
114

1,217,673
Messages
6,137,918
Members
450,098
Latest member
Ikmal Sabri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back