Sumif using offset and columns functions

0
votes

I want Excel to find a date and sum the preceding 12 cells in the row based on a keyed date.

I have 3 1/2 years of monthly financial data. I want to get the trailing twelve month totals based on a selected date. In my 'data input' tab row 111 is the month and row 112 are the respective revenues. H43 is my input cell for the date I want it to calculate from. 

=SUMIF('data input'!$D$111:$AR$111,$H$43,OFFSET('data input'!$D$112,0,COLUMNS('data input'!$D$112:$AR$112),1,-12))/1000

If I key in 4/30/2019 I want the formula to return the total revenues for MAY 2018 - APR 2019. When I step through the formula, it always references the last 12 columns in the array regardless of what date I input and the value comes back to 0.

1
excelexcel-formula
What are the actual dates in the column? You say MAY 2018 but then try to compare that to 4/30/2019 . Are all the dates actually the last day of the Month formatted as mmm yyyy or are they text? - Scott Craner
see here: stackoverflow.com/questions/56110105/… - Scott Craner

1 Answers

0
votes

What your OFFSET function is doing is first going to the end (because the start column=the third argument is set to you by the COlUMNS() formula, which is constant and equal to the length), and then going back 12 months. This is why the referenced ones are the last 12.

The SUMIF is wrong in many ways. You are trying to map an array of all (about 50?) columns to an array of 12. The first and last parameter of SUMIF must be the same length. Not only that, but you have added H43 as the criterion, which even if you got the last parameter correct, would only return you the one month you selected, and not the rest 12.

What you really need to do is simply SUM the correct range found by OFFSET. Try this:

=SUM(OFFSET('data input'!$D$112,0,-12+MATCH($H$43,'data input'!$D$111:$AR$111,0),1,12))

(Having a negative size feels alien to me, so I moved the starting point back by 12)

You might have to play around with MATCH a bit, eg using a conversion function from H43 to the values present in the 111 row.

And of course, you might have to do some editing on the formula if your H43 belongs to the first 11 columns.