Try this SUM(IF(...)) alternative, which needs to be array-entered, press
CTRL+SHIFT+ENTER to confirm the formula:
=SUM(IF((TRIM($A$3:$A$5)="RED")*(ISNUMBER($B$3:$B$ 5))*(ISNUMBER($F$3:$F$5)),$B$3:$B$5*$F$3:$F$5))

Above tested ok here, it will ignore text values in either col B or col F
which would otherwise cause the #VALUE! error in the earlier sumproduct
expression
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"RKS" wrote:
Hi Max,
When i m using =SUMPRODUCT(--(TRIM($A$3:$A$5)="RED"),$B$3:$B$5) this its
working fine. no error but when i m using
=SUMPRODUCT(--(TRIM($A$3:$A$5)="RED"),$B$3:$B$5*$F$3:$F$5) its show #VALUE!
error.

It means problem is only multiplication * $F$3:$F$5. In F column have some
blank & character. and colour row column have numeric like 1,2,3 etc.