Pulling totals from most current 4 dates that will update as data is added each week

[noviceexcelguy]

Hi!
I am needing some help with a spreadsheet. My spreadsheet has multiple clients and projects. It includes cost totals for each week of the project. I need to pull the last 4 weeks of cost totals for each client for a new tab. The formula needs to be able to update each time someone adds in a new weekly total for a project.

I'm not sure if that's enough info, but I'm happy to provide more.

Thanks in advance for your help!

[Tony Valko]

Will there ALWAYS be at least 4 entries to retrieve?

[noviceexcelguy]

To keep it simple, I'm going with "yes".

[noviceexcelguy]

And the weeks totals need to be added together. So basically, I need a total of the last 4 weeks for every client.

[FDibbins]

Hi, welcome to the forum

I'm not sure if that's enough info, but I'm happy to provide more.

It would help a lot if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Tony Valko]

Try something like this...

Data Range

	A	B	C	D	E	F	G	H
1	Client	------	------	------	------	------	------	------
2	Smith Bros	51	41	12	82	23	94
3

=SUM(OFFSET(B2,,COUNT(B2:Z2)-1,,-4))

The formula returns the sum of 12, 82, 23 and 94. =211

[noviceexcelguy]

How's this? {Not sure I'm doing the attaching correctly.}

[FDibbins]

Try this...
=SUMIF(A:A,">="&LARGE(A:A,4),C:C)

[noviceexcelguy]

Your formula works well. However, I really need to be able to pull data for one company. I attached an updated version of the spreadsheet that I will be using. You'll see that there are now 2 tabs (this more closely matches how my spreadsheet is setup). My goal is to be able to apply your formula for each company listed on sheet 1. Sheet 2 has consolidated information for multiple companies. For example, I would like to input your formula in B2 of Sheet 1 and only retrieve data for company 'Medical' from Sheet 2.

I appreciate your help!

[FDibbins]

See if this is what you want...
=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,">="&LARGE(Sheet2!$A:$A,4),Sheet2!$B:$B,Sheet1!A2)

[FlameRetired]

Another way.

This must be array-entered in B2 and filled down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[FlameRetired]

Or piggy-backing on Ford's formula ... array-entered also.

Please Login or Register  to view this content.

BTW so I could analyze as I was going I reduced the whole column references to 1000 rows.

[noviceexcelguy]

I appreciate your help. This formula sums the last 4 entries, regardless of date. I need to sum only entries dated within 4 weeks of the newest date. For instance, in the example spreadsheet I attached, I need a formula that only sums the entries for 'Power' dated from 1/3/2016 to 12/13/2015 (not just the last 4 entries). I've tried using the following formula:

=SUMIFS(Sheet2!$C:$C,Sheet2!$B:$B,Sheet1!A2,Sheet2!$A:$A,">="&MAX(Sheet2!$A:$A)-28)

I get the correct results for a couple of customers but not for all. I'm having trouble getting it to work for all Customers listed in column A, at the same time. I'm not sure what the problem is. I attached a newer version of the spreadsheet.

[noviceexcelguy]

I'm trying to get the sum for all dates that fall within a 4 week (28 days) range before the newest date, not just the last 4 entries. For instance, in the example spreadsheet I attached, I need a formula that only sums the entries for 'Power' dated from 1/3/2016 to 12/13/2015 (not just the last 4 entries). I've tried using the following formula:

=SUMIFS(Sheet2!$C:$C,Sheet2!$B:$B,Sheet1!A2,Sheet2!$A:$A,">="&MAX(Sheet2!$A:$A)-28)

I get the correct results for a couple of customers but not for all. I'm having trouble getting it to work for all Customers listed in column A, at the same time. I'm not sure what the problem is. I attached a newer version of the spreadsheet.

[hemesh]

Copy paste below and then hold control shift and then hit enter to make it array formula

=SUMIFS(Sheet2!$C:$C,Sheet2!$B:$B,Sheet1!A2,Sheet2!$A:$A,">="&MAX(IFERROR((Sheet2!B:B=Sheet1!A2)*(Sheet2!A:A),0))-28)