SUMPRODUCT Problem

[spectraflame]

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

 

[NBVC]

Try:

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

 

[spectraflame]

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

The results should be:

10 = 1
20 = 2
30 = 0

Book2
	A	B	C	D	E	F	G	H
1		METER	FOUND	SOURCE		COUNT	COUNT	COUNT
2	ROUTES	DB	MATCH	INTWACS		FOR10	FOR20	FOR30
3	10	10047753				2	2	2
4	10	10047754	10047754	10047754
5	20	10047746	10047746	10047746
6	20	10902027	10902027	10902027
7	30
8	30
Sheet1

 

[spectraflame]

Is there any reason why the formulas in F3,G3, & H3 are not good to use?

Book2
	A	B	C	D	E	F	G	H
1		METER	FOUND	SOURCE		COUNT	COUNT	COUNT
2	ROUTES	DB	MATCH	INTWACS		FOR10	FOR20	FOR30
3	10	10047753				1	2	0
4	10	10047754	10047754	10047754
5	20	10047746	10047746	10047746
6	20	10902027	10902027	10902027
7	30
8	30
Sheet1

 

[Aladin Akyurek]

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]

Aladin,

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

Thanks again for your knowledge.

Matthew