sumif with multiply-rks

[RKS]

hi
I have long sheet sheet which have many colour with size and CTN is No of
carton
I want to summary of each colour wise, size wise total qty.

A B C D E F
COLOUR <----SIZE-------> TOTAL CTN
S M L
RED 10 10 10 30 5
150 TOTAL QTY
BLACK 5 5 5 15 2
30 TOTAL QTY
RED 10 10 10 30 3
150 TOTAL QTY

I am using this formula =SUMIF($A$3:$A$5,"RED",$B$3:$B$5) ITS GIVE 20 which
is right. but i wnts its multiply by CTN column like 10*5 +10*3=80

anybody please help me how its possible. please urgent...

Thanks
RKS

 

[Max]

Try: =SUMPRODUCT(--($A$3:$A$5="RED"),$B$3:$B$5*$F$3:$F$5)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[Stefi]

=SUMPRODUCT(--(A3:A5="RED"),B3:B5,F3:F5)
Regards,
Stefi


„RKS†ezt írta:

 

[RKS]

Hi Max
Thanks for reply
its working in new sheet. but not working my sheet. i think my column f hv
some blank and some place in character. pls advice me or change ur formula so
its working both condition

waiting ur reply
once again thanks
rks

 

[Max]

Try wrap a TRIM around the text range for improved robustness:
=SUMPRODUCT(--(TRIM($A$3:$A$5)="RED"),$B$3:$B$5*$F$3:$F$5)

P/s: Pl press the YES buttons (like the one below) for ALL responses which
help to answer your query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[RKS]

hi Max

It Still show #VALUE! error

RKS

Try wrap a TRIM around the text range for improved robustness:
=SUMPRODUCT(--(TRIM($A$3:$A$5)="RED"),$B$3:$B$5*$F$3:$F$5)

P/s: Pl press the YES buttons (like the one below) for ALL responses which
help to answer your query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[RKS]

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.

RKS

 

[Max]

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