Formula to Sum Last 4 Rows of Non-Zero Data

[shouse01]

I have a large excel file that I update with weekly sales data, every week. I have a table that populates based off of a pivot table. We often look at key time periods other than just the last week (aka Last 4 Weeks, Last 8 Weeks, etc). So far, each week, I have been manually dragging the sum area down so it represents the last 4, 8, etc. weeks. For example (Where weeks 9 and 10 have not ocurred yet):


Week $ Sales
1 $100.00 L4 $2,300.00
2 $200.00 L8 $3,300.00
3 $300.00
4 $400.00
5 $500.00
6 $600.00
7 $600.00
8 $600.00
9 $0.00
10 $0.00


What formula can I add so that, when Week 9 occurs and the data gets added, the formula will automatically adjust to sum Weeks 6-9 (for L4) and 2-9 for L8? In essence, I need both the beginning value and the end value to be dynamic.


Thanks to anyone who helps! I'm new to the thread, so if this has been discussed before, sorry!!

[ragulduy]

one option:
=SUM(INDIRECT("A" & LARGE(IF(NOT(A1:A100=""),ROW(A1:A100),0),1) & ":A" & LARGE(IF(NOT(A1:A100=""),ROW(A1:A100),0),4)))
entered as an array formula (confirmed with ctrl+shift+enter)

[shouse01]

When I try it with the data given, I get $1,234 for Weeks 5-8, instead of $2,300. Am I doing something wrong?

[ragulduy]

It will count 0's as a value.

Assuming that you will never have a week with 0 that you want to count, you would want:
=SUM(INDIRECT("A" & LARGE(IF(A1:A100<>0,ROW(A1:A100),0),1) & ":A" & LARGE(IF(A1:A100<>0,ROW(A1:A100),0),4)))

[TheCman81]

Does something liek this work for you?

This is an array formula so needs to be entered with control shift enter

shouse01 Test.xlsx

[shouse01]

THAT WORKED! THANK YOU! Haha..... new issue now. We often compare to weekly sales from the prior year. Since all weeks have completed and have values, how do I sum the same rows that are summed in the current year's data? i.e. If I had column C where values for all 10 weeks were entered, how would I sum the same rows (Weeks 5-8) as the previous calculation?

[ragulduy]

In the formula I gave you, change "A" to whatever column you wish. So for column C:
=SUM(INDIRECT("C" & LARGE(IF(A1:A100<>0,ROW(A1:A100),0),1) & ":C" & LARGE(IF(A1:A100<>0,ROW(A1:A100),0),4)))

[martindwilson]

=sum(index($b$10000:index($b$2:$b$10000,large(index(($b$2:$b$10000<>0)*row($b$1:$b$9999),0),4)),0)) last 4
=sum(index($b$10000:index($b$2:$b$10000,large(index(($b$2:$b$10000<>0)*row($b$1:$b$9999),0),8)),0)) last 8