How to sum values based on criteria ranges that are both columns and rows?

Question

I am trying to write a sum formula that will return the summed value of a unique reference between 2 dates.

On my range table (the one I am trying to sum) the dates are in row 9 (from columns K to IP) and the unique reference is in column B (B10 to B139 (The number of rows varies)).

On the my results table (where I am trying to write the formula) there is a list of all the unique references that appear on sheet A.

Cell I7 contains the start date and J7 contains the end date of the dates I'd like to sum between.

All the formula that I have tried returns a #Value error:

=SUMIFS(Output!K10:IP139,Output!B:B,G12,Output!9:9,">="&I7,Output!9:9,"<="&J7)

^I realise this will not work as all the criteria ranges need to be in columns. *for SUMIF formulas as far as i'm aware

=SUM(IF(Output!K9:FG9>='Stock + Demand'!I7,IF(Output!K9:FG9<=J7,IF(Output!B:B=G11,Output!K11:IP139))))

^Also returns #Value error.

I also tried a Sumproduct formula, but this just overloaded my excel ("Excel ran out of resources whilst trying to calculate" message), and so do not know if this would have returned the desired result.. either way I need a formula that can work on worksheets of the same type but with 2000+ rows of data.

I think some images showing the worksheets would be helpful but my work PC does not allow me to save images (very frustrating). I'll try and upload some when I get home.

EDIT - SOLVED

Here is the formula that worked:

=IFERROR(SUM(OFFSET(Output!$A$1,MATCH(SUMIF(Output!$B:$B,$G11,Output!$A:$A),Output!$A:$A,0)-1,MATCH(I$7,Output!$9:$9,0)-1):OFFSET(Output!$A$1,MATCH(SUMIF(Output!$B:$B,$G11,Output!$A:$A),Output!$A:$A,0)-1,MATCH(J$7,Output!$9:$9,0)-1)),"")

Output being the data worksheet, OutputA:A and B:B being reference columns, I7 and J7 being start and end date respectively, Output 9:9 being the date lookup row and G11 being the unique value to lookup in Output B:B.

Answer 1

I found an excel guru in the office and he wrote a formula that worked using MATCH and OFFSET, there are a couple of columns of unique values to the left of the data table that could be used to identify the row number to sum from and similar for setting the range for the dates.. I'm at home now and don't have the formula to hand but I do have an example of the sheet I wanted to work with (may help someone like me):

Table

I had the start and end date of each month on my results tab, as well as a list of item numbers and some of the concatinations in column B (only the "frame order" ones).

I'll post the formula tomorrow. Just tried recreating it and failed miserably :(

EDIT

Here is the formula that worked:

=IFERROR(SUM(OFFSET(Output!$A$1,MATCH(SUMIF(Output!$B:$B,$G11,Output!$A:$A),Output!$A:$A,0)-1,MATCH(I$7,Output!$9:$9,0)-1):OFFSET(Output!$A$1,MATCH(SUMIF(Output!$B:$B,$G11,Output!$A:$A),Output!$A:$A,0)-1,MATCH(J$7,Output!$9:$9,0)-1)),"")

Output being the data worksheet, OutputA:A and B:B being reference columns, I7 and J7 being start and end date respectively, Output 9:9 being the date lookup row and G11 being the unique value to lookup in Output B:B.

Answer 2

This is an array formula and will need to be confirmed with Ctrl + Shift + Enter

{=SUM(IF(Dates>=I7,IF(Dates<=J7,IF(References=G12,Data))))}

Named ranges are:
- Dates: K9:IP9
- Data: K10:IP139
- References: B10:B139

Essentially the if statements are building an array of the data as either the desired value or FALSE, this is then being passed to SUM() for the addition. Though if this is a per row formula, you might as well SUMIFS(Data,Dates,">="&I7,Dates,"<="&J7) each row separately to avoid using array formulas.