Sum by Moving Dates

[ld1551]

I need help with a formula for a moving date range.

If today = Monday then sum the volume for the three dates for the previous Friday-Sunday. Otherwise, sum the volume for yesterday's date.

Any ideas?

Thank you.

[ChemistB]

Can you upload an example spreadsheet showing what you are trying to do?

Best guess, not knowing exactly what sort of data we're looking at. With Dates in Col A starting at A2, and volumes in Col B

Please Login or Register  to view this content.

[ld1551]

I will try your suggestion and see how it goes. On a summary tab I want to be able to show what has shipped each previous day, but the caveat is that on Mondays I need to know what shipped Friday, Saturday, and Sunday and the data that feeds the report has 9 rolling days of data in it. But yes, Column A would have the volume and Column B would have the dates.

[Bo_Ry]

Please try

C1 =Today()

C2 =SUMIFS(A:A,B:B,">="&WORKDAY(C1,-1),B:B,"<"&C1)

[ld1551]

Works great! I have one more level of complexity to add if you could assist further. There are 7 delivery destinations, or Column "C". How can I incorporate another level of IF criteria, that says if this destination then sum as formula has been written (by previous date if not Monday as you provided). I've tried adding it as a criteria 3 range and criteria 3 but it returns zero which I know is incorrect.

[Bo_Ry]

E1 =TODAY()

E2 =SUMIFS(A:A,B:B,">="&WORKDAY(E1,-1),B:B,"<"&E1,C:C,C2)
C2 can be change to "NY" or other destination

[ld1551]

Could C2 just be a range of locations? I have a summary sheet with the 7 locations, each having their own column.For Instance C2:I2?

[JeteMc]

It may be helpful if you could provide a sample of the summary sheet.
Let us know if you have any questions.