Conditional Total based on date

[jsalotra]

Hi,

Please help to me to create a formula where the total of columns can be achieved before a said date. I tried sumifs function but did not work out.
For example the total of columns should be 4 days prior to todays date. Right now, I am manually changing the formula. Please see the attached spreadsheet where I need formula in cell Z36 of row 29 for all days prior to 24th July 2017.

Thanks,
Jai

[oeldere]

z36 =SUM(INDIRECT(ADRESS(29,match(Z34,$A$11:$Y$11,0))):Y29) => result 64

Edit:

I read your question not well enough (I suppose)

since you asked for prior to here the formula for that.

z36=SUM($C$29:INDIRECT(ADRESS(29,match(Z34,$A$11:$Y$11,0)))) => result 51

[jsalotra]

Hi,

Thanks for your reply but I am not getting the required answer. The total from Dates July 24 & prior should be 51. Please see the attached file and let me know.

Thanks a lot!

[oeldere]

See the edit in #2.

[jsalotra]

Hi,

When I cut & Paste into separate sheet (sheet2), the formula is not working. Please see the attached.

Also, would this formula work if the pivot table changes for more dates and more rows. Everyday data is being added to spread sheet and then I do a pivot table and calculate the totals for 4 days prior to current day.

Thanks,

[oeldere]

b13=SUM(Sheet1!$C$29:INDIRECT("Sheet1!"&ADRESS(29,MATCH(B9,Sheet1!$A$11:$Y$11,0))))

See the attached file.

[oeldere]

And for your second question:

B13 =SUM(INDIRECT("Sheet1!$C$"&MATCH("Grand Total",Sheet1!$A$1:$A$38,0)):INDIRECT("Sheet1!"&ADRESS(MATCH("Grand Total",Sheet1!$A$1:$A$38,0),MATCH($B9,Sheet1!$A$6:$Y$6,0))))

See the attached file.

[oeldere]

Thanks for the rep.

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[oeldere]

Thanks for marking the question closed.

But why do you not solve this question using the pivot table?