Sum Formula Tip

[davinosky]

How can I sum only the last three values digits on a range of cells?

 

[fred]

Can you show a sample please?

 

[fred]

Can you show a sample please?

 

[davinosky]

As for instance, I have a 31 days range cells for a month and I want to only consider the sum of the last three values corresponding to the last three days of the ongoing month.

It is like today which is the 24th, I would like to sum the values corresponding to day 22, 23 & 24. but by tomorrow the formula must change to consider the sum of the values for 23, 24 & 25 and thus, so on and on!

 

[fred]

let's say your days are on column A down and data on column B down. Then all you need

[pre]

A        B          C
Date    Data     sum of last 3 value
1       67
2       85
3       40       =SUM(B2:B4)
4       62       =SUM(B3:B5)
5       49       =SUM(B4:B6)
6       56       =SUM(B5:B7)
7       80       =SUM(B6:B8)
8       88       =SUM(B7:B9)
9       61       =SUM(B8:B10)
10      77       =SUM(B9:B11)
11      37       =SUM(B10:B12)
12      79       =SUM(B11:B13)
13      78       =SUM(B12:B14)
14       1       =SUM(B13:B15)
15      96       =SUM(B14:B16)
16      70       =SUM(B15:B17)
17      31       =SUM(B16:B18)
18      28       =SUM(B17:B19)
19      13       =SUM(B18:B20)
20      52       =SUM(B19:B21)
21      83       =SUM(B20:B22)
22      83       =SUM(B21:B23)
23      72       =SUM(B22:B24)
24      79       =SUM(B23:B25)
25      25       =SUM(B24:B26)
26      85       =SUM(B25:B27)
27      37       =SUM(B26:B28)
28      16       =SUM(B27:B29)
29      29       =SUM(B28:B30)
30      82       =SUM(B29:B31)
31      23       =SUM(B30:B32)

[/pre]

 

[fred]

don't mix up with the data and the formula. the middle number is the data in column B and the formula on the right is on column C.

 

[davinosky]

Remember this depends on the date. So how could I set this formula to automaticaly move according to the ongoing date.


I am trying to make a template for a re-order point of an item in inventory. so the formula will be Qty of the purchase order will be =Point of re-order - maximun inventory + demand for the last three days. HHHEEEEELLLLPPP!

 

[davinosky]

P.S.

THANKS A LOT!

 

[Montrey]

Davinosky,


Please post some sample data. Your question seems straight forward but we don't have any data to work with. Making it a guessing game.

 

[Luke M]

Using Fred's data as an example, a single formula to gather last 3 days would be:

=SUM(OFFSET(INDEX(B:B,COUNTA(B:B)),,,-3,1))