SUMPRODUCT Problem

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

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

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
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
 

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
Aladin,

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

Thanks again for your knowledge.

Matthew
 

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

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
Top