• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sums multiple columns

vijay.vizzu

vijay.vizzu

Member
Dear All,


currently i m using this formula =SUM(VLOOKUP(K3,tbl_1,3,FALSE),VLOOKUP(L3,tbl_1,3,FALSE)) to sum the criterias value with anothers criterias value. but if any one cell in lookup value will blank, then the formula gives error #N/A. So kindlly sort out this problem.
 
model name jan'12 feb'12 mar'12 apr'12 may'12

FZ Series 22920 25120 24922 25494 25676

SZ Series 8596 9544 10688 10600 11680

R15 Series 3194 3696 3588 3804 3882

YBR125 Series 4278 3838 3914 4342 4218

YD125 Series 2368 2000 2624 2000 2000

YBR110 1460 2100 1920 2215 2500

YD110 3314 3520 3656 3942 4364

Scooter 0 0 0 0 0
 
Your table is in between A1:F18, Start Month, End Month, Type in I10, I11, I12 respectively and final result in I13:

[pre]
Code: 
Type	        Jan-12	Feb-12	Mar-12	Apr-12	May-12
FZ Series	22920	25120	24922	25494	25676
SZ Series	8596	9544	10688	10600	11680
R15 Series	3194	3696	3588	3804	3882
YBR125 Series	4278	3838	3914	4342	4218
YD125 Series	2368	2000	2624	2000	2000
YBR110 1460	2100	1920	2215	2500	0
YD110 3314	3520	3656	3942	4364	0
Scooter 	0	0	0	0	0
[/pre]
Use this formula: =SUMPRODUCT((A11:A18=I12)*(B10:F10>=I10)*(B10:F10<=I11),B11:F18)


..entered in I13. its easier then vlookup().

NB: Format months as mmm-yy" is needed.


Regards,

Faseeh
 
Dear All,


Please use this link.


https://skydrive.live.com/redir.aspx?cid=0f12e9e0eb2e56a4&resid=F12E9E0EB2E56A4!120&parid=F12E9E0EB2E56A4!116&authkey=!AE6wUxcfWo6MN6s
 
Hi Vijay ,


You can try the following formula :


=IFERROR(SUMPRODUCT(--(Monthly_volume!$C$4:$C$18=(OFFSET($K3,0,0,1,COUNTA($K3:$R3)))),((Monthly_volume!G$4:G$18)*ISTEXT(OFFSET($K3,0,0,1,COUNTA($K3:$R3))))),0)


Enter it in T3 , copy it across and downwards.


And another point - replace all your SUM formulae by SUBTOTAL(9,...) so that the SUBTOTAL function takes care not to sum twice. If you use =SUBTOTAL(9,G4:G5) in G6 , =SUBTOTAL(9,G9:G11) in G12 and =SUBTOTAL(9,G13:G14) in G15 , then your formula in G19 can be :


=SUBTOTAL(9,G4:G18)


The SUBTOTAL function takes care not to sum the individual subtotalled cells which come in between.


Narayan
 
Hi, vijay.vizzu!


At risk or being silly, did you tried something like this?

=SUM(IF(ISERROR(VLOOKUP(K3,tbl_1,3,FALSE)),0,VLOOKUP(K3,tbl_1,3,FALSE)),IF(ISERROR(VLOOKUP(L3,tbl_1,3,FALSE))),0,VLOOKUP(L3,tbl_1,3,FALSE)))


It's your same formula but replacing every member by zero if it is blank.


Hope it helps you.


Regards!
 
Hi vijay.vizzu,


There is a slight change in outlook of this file, it does not effect the original table in any way at all. See this:


http://dl.dropbox.com/u/60644346/vijay.vizzu_Reworked.xlsx


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top