Index & match multiple columns

[cajste]

Hi,

As part of our monthly report I prepare a financial summary with actuals, budget and forecast per for the month, year to date and full year. Actuals, budget and forecast figures are in different sheets and collecting them for a single month is no problem, I use this index - match formula which makes updating real easy

=INDEX('2015A'!$D12:$O12;MATCH($C$1;'2015A'!$D$2:$O$2;0))

This formula does not work for year to date though since it can only collect data from one column. Is it possible to tweak the formula to collect data from multiple columns? Or are there other formulas that can be used?

I know that I can create a year to date table and use the above formula on that table but if it's possible to avoid it I would preferer to do so.

//C

 

[gaz_chops]

Look at sumif.

 

[cajste]

I tried that the sumif but haven't been able to get it to work.

Below is an example of the table I'm working with, to give some context. Sorry for the poor formating but if pasted into Excel it looks as it should.

In my financial summary I need to get to get 2 values from the Total Revenues row. Given that the current month is April I collect 11 497 as this month figure with an Index/match formula, no problem doing that. The other value I need to get is YTD, which is 48 259 in this case, and this is what I haven't been able to solve. With SUMIF, the criteria would need to be equal or less than 2015-04, but SUMIF doesn't seem to allow >, = etc. in the criteria part. Or am I missing something?

2015-01 2015-02 2015-03 2015-04 2015-05
Total Revenues 12 255 11 688 12 819 11 497 12 652
Revenue 1 10 411 9 842 10 960 9 672 10 794
Revenue 2 1 844 1 846 1 859 1 825 1 859
Cost 1 -534 -534 -538 -528 -538
Cost 2 -1 807 -1 732 -1 942 -1 701 -1 941
Cost 3 -4 000 -3 879 -4 382 -3 556 -4 191

//C

 

[gaz_chops]

In sumif enter as "<="&cellref (or date)You must use " either side as above

 

[cajste]

Brilliant, thanks!

I tried to get it to sum multiple rows, the formula looking like below, but it doesn't return the correct number. Am I doing something wrong?

=SUMIF('2015'!D2:O10;"<="&C1;'2015'!D7:O10)

//C

 

[gaz_chops]

You said you wanted to sum one row, "the Total Revenues row".
If that is the case, use sumif, but your formula should address 1 row with the dates & one row with the values, also the ranges need to match.
=SUMIF($B$1:$F$1,"<="&$A$11,$B$2:$F$2)

If you need to sum multiple rows try
=SUMPRODUCT(($B$1:$F$1<=$A$11)*($B$2:$F$4))

Your sample above,
Returns 48259 for total revenue using sumif.
Returns 96518 for total revenue & Rev 1 & Rev 2 using sumproduct.

[TABLE="width: 407"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]Jan-15[/TD]
[TD="align: right"]Feb-15[/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]Apr-15[/TD]
[TD="align: right"]May-15[/TD]
[/TR]
[TR]
[TD]Total Revenues[/TD]
[TD="align: right"]12255[/TD]
[TD="align: right"]11688[/TD]
[TD="align: right"]12819[/TD]
[TD="align: right"]11497[/TD]
[TD="align: right"]12652[/TD]
[/TR]
[TR]
[TD]Revenue 1[/TD]
[TD="align: right"]10411[/TD]
[TD="align: right"]9842[/TD]
[TD="align: right"]10960[/TD]
[TD="align: right"]9672[/TD]
[TD="align: right"]10794[/TD]
[/TR]
[TR]
[TD]Revenue 2[/TD]
[TD="align: right"]1844[/TD]
[TD="align: right"]1846[/TD]
[TD="align: right"]1859[/TD]
[TD="align: right"]1825[/TD]
[TD="align: right"]1859[/TD]
[/TR]
[TR]
[TD]Cost 1[/TD]
[TD="align: right"]-534[/TD]
[TD="align: right"]-534[/TD]
[TD="align: right"]-538[/TD]
[TD="align: right"]-528[/TD]
[TD="align: right"]-538[/TD]
[/TR]
[TR]
[TD]Cost 2[/TD]
[TD="align: right"]-1807[/TD]
[TD="align: right"]-1732[/TD]
[TD="align: right"]-1942[/TD]
[TD="align: right"]-1701[/TD]
[TD="align: right"]-1941[/TD]
[/TR]
[TR]
[TD]Cost 3[/TD]
[TD="align: right"]-4000[/TD]
[TD="align: right"]-3879[/TD]
[TD="align: right"]-4382[/TD]
[TD="align: right"]-3556[/TD]
[TD="align: right"]-4191[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Apr-15[/TD]
[TD="align: right"]48259[/TD]
[TD="colspan: 4"]=SUMIF($B$1:$F$1,"<="&$A$11,$B$2:$F$2)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]96518[/TD]
[TD="colspan: 4"]=SUMPRODUCT(($B$1:$F$1<=$A$11)*($B$2:$F$4))[/TD]
[/TR]
</tbody>[/TABLE]

 

[cajste]

Works great, thanks!!!

 

[gaz_chops]

You're welcome.