SUMPRODUCT Problem

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
829
Office Version
  1. 365
Platform
  1. Windows
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).
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Aladin,

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

Thanks again for your knowledge.

Matthew
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top