Trailing 12 month Table (Issue while using OFFSET + COUNT function)

[ryanwren]

Dear,

I have to create a table to include formulas to retrieve the past 12 months data from another table what has data from Jan-15 to Feb-16.
I want the new table to record the recent 12 months data automatically.

I tried using the OFFSET function with count function but it doesn't seem to work when there is a blank cell, or when the reference table automatically updates the value for upcoming month (say Mar-16).

My reference table has its headers(months) from cell B9 to Y9, data obtained by formulas spread through B10 to Y15.

OBS Status	Jan-15	Feb-15	Mar-15	Apr-15	May-15	Jun-15	Jul-15	Aug-15	Sep-15	Oct-15	Nov-15	Dec-15	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16
OPEN	0	0	0	0	0	0	0	2	0	3	5	11	46	38
CLOSED	7	41	20	33	74	71	93	131	61	124	247	234	225	164
POSITIVE OBS	0	3	1	5	6	4	6	7	3	14	21	11	30	17

<colgroup><col><col><col><col><col span="2"><col><col span="3"><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>

My new table show retrieve data from the above table for last 12 months, it should include blank cells as well

This is the formula i used in my new table
=OFFSET(B10,0,COUNT(10:10)-12,1,1)


I appreciate any help.
Thanks in advance.

Kind Regards,
Brian George.

 

[FDibbins]

Sounds like you need to use SUMIFS() for this

If your dates are real dates, and not text, you could use something like...
=SUMIFS(B10:Y10,$B$9:$Y$9,"<="&start-date,$B$9:$Y$9,">"&edate(starte-date,*12)
where start-date can be a reference to a cell containing the start date (prefered), or you can hard code oi