|
sumif with multiply-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 |
sumif with multiply-rks
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 --- "RKS" wrote: 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 |
sumif with multiply-rks
=SUMPRODUCT(--(A3:A5="RED"),B3:B5,F3:F5)
Regards, Stefi €˛RKS€¯ ezt Ć*rta: 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 |
sumif with multiply-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" wrote: 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 --- "RKS" wrote: 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 |
sumif with multiply-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" wrote: 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 |
sumif with multiply-rks
hi Max
It Still show #VALUE! error RKS "Max" wrote: 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" wrote: 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 |
sumif with multiply-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 "RKS" wrote: hi Max It Still show #VALUE! error RKS "Max" wrote: 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" wrote: 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 |
sumif with multiply-rks
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. |
| All times are GMT +1. The time now is 10:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com