Sum Index Match Arrays

**CaesarBob** · 09-14-2012, 05:08 PM

Hi,

I'm trying to write a formula which will allow me to track a Year-to-Date value based on a budget. As in this example:

Sum Index Match Array.PNG

Based on this, i want to be able to calculate the year-to-date value assuming we are in September (C21) for Cost1 & Cost2. i.e. the value i want is -342 (sum(B5:J6)). But if i change the month in C21 to October the new value would be -390 (sum(B5:K6)).

I have tried a couple of combinations that don't quite get what i'm after:

=SUM(HLOOKUP(C21,$A$1:$M$8,{5,6},0))

or

=SUM(INDEX($A$1:$M$8,{5,6},MATCH(C21,A1:M1,0),1))

The first effort gives me the right number for the current month (sum(J5:J6)) and the second only gives (J5). But i can't expand this to the full y-t-d.

Has anyone got a idea on this? Ideally without using helper columns if possible.

Thanks
Bob.

**FDibbins** · 09-14-2012, 05:22 PM

not every1 can open .png files, myself included. It would be far better if you could upload a sample of your file, showing what you have, and an example of your expected outcome. Thanks

**daddylonglegs** · 09-14-2012, 05:24 PM

Hello Bob, there are several possibilities, e.g.

=SUM(B5:INDEX(B6:M6,MATCH(C21,B$1:M$1)))

or

=SUMPRODUCT((B$1:M$1<=C21)*B5:M6)

**CaesarBob** · 09-14-2012, 05:55 PM

Blimey, that's quick for a Friday evening!

Thanks Daddylonglegs that will do nicely.

Cheers.

**CaesarBob** · 09-18-2012, 11:31 AM

Although my original question has been answered, and the =SUMPRODUCT((B$1:M$1<=C21)*B5:M6) is the option i've gone with, i've now got a follow on problem that i need help with.

Based on the above sample data i need to modify daddylonglegs formula so that i can calculate a Qtr value. i.e. i would have an additional reference field in C22 for Qtr start month e.g 01-Jul-2012 and then the formula needs to sum the Cost1 & Cost2 values (H5:J6) and if C22 was amended to 01-Apr-2012 it would sum (E5:G6).

Thanks
Bob.