SUM using Dynamic References via HLOOKUP

[pflam]

Hi,

On Sheet 1 I have:

A row of consecutive monthly dates (Jan-14, Feb-14, Mar-14, Apr-14 etc.) - let's call them A1-Z1
A row below with Sales figures for each month (1,2,3,4,5 etc.) - let's call them B1 - Z1

On Sheet 2 I have:

A row of dates which represent different period end dates which are not all the same length (eg. Mar-14, Dec-14, Dec-15, Dec-16, Jun-17 etc.) - let's call them A1 - H1

I would like to create a row under these period end dates (let's call them A2 - H2) which return the Sales figure associated with the period from one date to the next (i.e. the number under Dec-14 should sum Sales from Mar-14 to Dec-14) by looking at the monthly figures in Sheet 1.

I considered using HLOOKUP to get the monthly figures from Sheet 1 and then somehow summing them but am at a bit of a loss as to how to make the SUM reference a dynamic function of the period end dates on Sheet 2.

Any ideas would be welcome.

Thank you.

 

[anglais428]

[TABLE="width: 540"]
<colgroup><col><col><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Apr-14[/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]Jul-14[/TD]
[TD="align: right"]Aug-14[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8
[/TD]
[/TR]
</tbody>[/TABLE]

Lookup in cell C4: Jul - 14
Formula: =SUMIF((A1:H1),"<="&VALUE(C4),A2:H2)
Result: = 28

Adjust to your ranges[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]



[/TD]
[/TR]
</tbody>[/TABLE]

 

[pflam]

Thank you for the response. Not sure if i'm missing something obvious but appreciate if you can expand slightly on the concept, particularly how SHeet 1 and Sheet 2 interact in the solution.

In particular, and going by the example I provided, is C4 just the date cell on Sheet 2 (from rows A1-H1)?
Do you mind just explaining the formula and confirming that it allows me to sum monthly numbers for the irregular periods on Sheet 2?

Thanks.

 

[anglais428]

Yes C4 would be a cell on your Sheet2. You would just make the Sheet1 range absolute then drag the formula across on Sheet2.
This formula will count those months that come before the month in question on Sheet2. If you need irregular periods, you will need to add a second condition and convert the SUMIF to SUMIFS.

 

[pflam]

For clarity, regarding the first question above;

If the correct formula is =SUMIF((A1:H1),"<="&VALUE(C4),A2:H2) my main question is how can I get a dynamic value for (C4) based on the relevant month in Sheet 2? As mentioned Sheet 2 has period end dates like Mar-14, Dec-14, Dec-15, Dec-16, Jun-17 etc. so I would like to know how to reference the corresponding cell in Sheet 1 so that it substitutes (C4) in a dynamic way in the formula.

thanks.

 

[pflam]

An Example might help to illustrate:

Sheet 1 - top 2 rows:

[TABLE="width: 1018"]
<colgroup><col><col span="2"><col><col><col><col><col span="2"><col span="2"><col><col><col span="2"><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Apr-14[/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]Jul-14[/TD]
[TD="align: right"]Aug-14[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Oct-14[/TD]
[TD="align: right"]Nov-14[/TD]
[TD="align: right"]Dec-14[/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]
[TD="align: right"]Jun-15[/TD]
[TD="align: right"]Jul-15[/TD]
[TD="align: right"]Aug-15[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Oct-15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 - top row:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Mar-14[/TD]
[TD="class: xl65, width: 64, align: right"]Dec-14[/TD]
[TD="class: xl65, width: 64, align: right"]Dec-15[/TD]
[TD="class: xl65, width: 64, align: right"]Dec-16[/TD]
[TD="class: xl65, width: 64, align: right"]Jun-17[/TD]
[/TR]
</tbody>[/TABLE]

Now under Mar-14 I am putting =SUMIF((Sheet1!$A$1:$AJ$1),"<="&VALUE(A1),Sheet1!$A$2:$AJ$2) but its just returning 3.
Furthermore, when i extend the formula so under Dec - 14 it is =SUMIF((Sheet1!$A$1:$AJ$1),"<="&VALUE(B1),Sheet1!$A$2:$AJ$2) it returns 66.

In both instances it doesnt seem to be summing all previous numbers. Appreciate if you can point out what im doing wrong.

 

[pflam]

Apologies, just ignore me....long day. I see the issue (at least with the C4 comment). thanks