• 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.

How to use SUM function with condition

C

croft

New Member
Hello


I just found this nice site while trying to find help to an excel problem. Maybe you can help me.


I would need help creating a formula which calculates sum of a range with fixed starting value and variable ending value.


On sheet1 I have months on columns ja sales volumes of different products on rows

like this:


Sheet1


A B C D etc.

1 Jan Feb Mar etc.

2 product1 100 105 90

etc.


On sheet2 I have summary table where I sum year to date volumes from January to current month.


Sheet2


A B C D

1 Mar YTD

2 product1

3


On sheet2 cell B2 I would like to have formula that sums the sales volumes of product 1 on sheet1 from January to which ever month I enter in cell A1 on sheet2.


I tried this formula, but it only sums values of January and the month entered on sheet2 A1, not the range between January and entered month.


=SUM(Sheet1!B2;VLOOKUP(A2;Sheet1!A2:M2;MATCH(A1;Sheet!A1:M1;0)))


Any help is appreciated.
 
Hi croft,


Welcome to the forums! Assuming that your data is present in Sheet1 between A1:E7 like this:

[pre]
Code: 
S.No    Product	        Jan	Feb	Mar
1	Product1	100	105	90
2	Product2	300	400	200
3	Product3	100	500	100
4	Product4	100	300	100
5	Product5	200	500	100
6	Product6	100	100	300

...this formula could be used:

[pre][code]=SUM(Sheet1!$C7:INDEX(Sheet1!$B7:E7,0,MATCH($B7,Sheet1!$C$1:E$1,0)+1))
[/pre]
Output table (Sheet2: Between A1:C7)

Product Month YTD
Product1 Jan 100
Product2 Mar 900
Product3 Jan 100
Product4 Feb 400
Product5 Mar 800
Product6 Jan 100[/code][/pre]
PS: Things could be really simple if you just format the month header row 'mmm-yy'! Is it possible?


Regards,

Faseeh
 
kchiba: I am not able to get that formula to work for some reason. :/ And I am having troubles really understanding this OFFSET formula in general.
 
Thank you Faseeh for the formula, I'll try that.


And yes, it is possible to format the month header row as 'mm-yy'.
 
Thank you Faseeh, the formula worked perfectly!


I would still be interested to learn if there is another way to do this.
 
Hi croft,


You are welcome any time! With mmm-yy formatting you can use sumproduct() as well. For similar condition as described in my last post it would also work:


=SUMPRODUCT((Sheet1!$B$2:$B$7=Sheet2!$A2)*(Sheet1!$C$1:E$1>=Sheet1!$C$1)*(Sheet1!$C$1:$E$1<=Sheet2!$B2),Sheet1!$C$2:$E$7)


...will give you this table:

[pre]
Code: 
Product	        Month	YTD
Product 1	Jan-12	100
Product 2	Mar-12	900
Product 3	Jan-12	100
Product 4	Feb-12	400
Product 5	Mar-12	800
Product 6	Jan-12	100
[/pre]
...keep visiting the forum.


Regards,

Faseeh
 
Hi, croft!


Let's say you have in first sheet starting at A1:

-----

[pre]
Code: 
Item	01/01/2012	01/02/2012	01/03/2012	01/04/2012	01/05/2012	01/06/2012	01/07/2012	01/08/2012	01/09/2012	01/10/2012	01/11/2012	01/12/2012
Product 	1		7		9		10		10		6		2		1		10		9		10		1		4
Product B	10		8		7		1		2		8		4		2		6		9		7		4
-----


And in second sheet starting too at A1:

-----

Item		01/06/2012
Product 1	42
Product B	28
[/pre]
-----


In this sheet enter in B1 the date up to which you want to sum, and in B2 type and copy down:

=SUMA(INDIRECTO(CONCATENAR("Hoja1!$B";FILA();":";EXTRAE(" ABCDEFGHIJKL";COINCIDIR(B$1;Hoja1!$1:$1;0);1);FILA()))) -----> in english: =SUM(INDIRECT(CONCATENATE("Sheet1!$B",ROW(),":",MID(" ABCDEFGHIJKL",MATCH(B$1,Sheet1!$1:$1,0),1),ROW())))


Regards!
 
Hi SirJB7,


This formula is not getting computed could there be any problem from my side? and how did you set thing for R1C1 configuration?? Please explain!


Faseeh
 
Hi, Faseeh!


Here's the link to the related file:

http://dl.dropbox.com/u/60558749/How%20to%20use%20SUM%20function%20with%20condition%20%28for%20croft%20at%20chandoo.org%29.xlsx


Give a look at the formula in Hoja2!B2.


Briefly I tried to build a formula that summed $B<row> thru <column><row> in sheet Hoja1.

How? Without trying to emulate formula forensic's articles it's as follows:

[pre]
Code: 
=SUM(
INDIRECT(
CONCATENATE(
"Sheet1!$B",
ROW(),
":",
MID(
" ABCDEFGHIJKL",
MATCH(
B$1,
Sheet1!$1:$1,
0
),
1
),
ROW()
)
)
)
[/pre]
From inside out:


- MATCH part retrieves the column number where the given date B1 is located in Hoja1.

- MID gives values from "B" thru "M" (12 values, one for each month) representing the column letter. I've made a slight adjustment because of last month wasn't included, changing " ABCDEFGHIJKL" to " BCDEFGHIJKLM".

- CONCATENATE arms the string "Sheet1!$B" & <row> (in this case 2) & ":" & <MID given value> & <row>

- INDIRECT needed for ranging

- SUM as requested


A little tricky but I found it funny.


Regards!


PS: I apologize for the last month exclusion, I'll promise it won't happen again... until next occurrence!
 
Hi, Faseeh!


You're welcome. Glad to have helped you.

BTW I use a lot the combination of INDIRECT function calling a CONCATENATE function when I have to do something unusual with unusual ranges. If no Excel function arises as suitable and the range has certain "logic" or "structure", well, then there's nearly nothing that resists to be constructed and then referred.

And the more complex the formula in characters, the most you'll get paid... :)


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