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

Using SUM and INDEX together

H

Hartke

New Member
I have a large table of data and I am looking to sum only part of it. Here's how the data is laid out:


Account# Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec


What I'd like to be able to do is to sum part of the data of the row, let's say from May - Nov, for a second list of account numbers.


It occurred to me to use SUM in combination with INDEX/MATCH. So my formula would look something like this:


=SUM((INDEX(MATCH($A1,$B:$B,0),MATCH("May",5:5,0)):INDEX(MATCH($A1,$B:$B,0),MATCH("Nov",5:5,0)))


The individual MATCHes return the right coordinates, but the formula doesn't work. Any clues would be appreciated.


Also, if there is a better way to go about this please give me your opinion.
 
Hi Hatrke,


Try this:


Where A2:A5 is the list of names, B1:M1 is the months


`=SUM(INDEX($B$2:$M$5,MATCH("xxx",$A$2:$A$5,0),MATCH("May",$B$1:$M$1,0)):INDEX($B$2:$M$5,MATCH("xxx,$A$2:$A$5,0),MATCH("Nov",$B$1:$M$1,0)))


Note: I use a simliar construct but put the required month range in different cells that can be changed. The alternative is to use the OFFSET formula
 
Here are to Formulas, one uses INDEX and the other OFFSET


Below is the range A1:D3 and the data table is in A6:M11


Name From to Result

xxx May Nov 56 =SUM(INDEX($B$7:$M$11,MATCH($A2,$A$7:$A$11,0),MATCH($B2,$B$6:$M$6,0)):INDEX($B$7:$M$11,MATCH($A2,$A$7:$A$11,0),MATCH($C2,$B$6:$M$6,0)))

xxx 5 11 56 =SUM(OFFSET($A$6,MATCH($A3,$A$7:$A$11,0),$B3,1,$C3-$B3+1))
 
Hi Hartke,


Regarding some working around for the issue sumproduct() could be a good option. Your data is located in cells A1:E6 (incl. Headers), your start date in H1, end date in H2 and you are entering this formula in H3,

[pre]
Code: 
A       B       C       D       E        F      G       H
Acct	Jan-12	Feb-12	Mar-12	Apr-12		Start 	Jan-12
XXX1	2000	1000	1000	5000		End	Mar-12
XXX2	1000	3000	4000	5000		Amount	4000
XXX3	3000	1000	5000	1000
XXX4	5000	5000	3000	4000
XXX5	2000	3000	3000	1000
Here is the formula:

[code]=SUMPRODUCT((A2:A6="XXX1")*(B1:E1>=H1)*(B1:E1<=H2),B2:E6)
[/pre]
NB: Remember to format the Month row as mmm-yy[/code] instead of simplex text!


Regards,

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