Calculate the most recently entered data in columns?

[mreynolds1775]

[TABLE="width: 1101"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Description
[/TD]
[TD="align: right"]9/1/2017
[/TD]
[TD="align: right"]9/2/2017
[/TD]
[TD="align: right"]9/3/2017
[/TD]
[TD="align: right"]9/4/2017
[/TD]
[TD="align: right"]9/5/2017
[/TD]
[TD="align: right"]9/6/2017
[/TD]
[TD="align: right"]9/7/2017
[/TD]
[TD="align: right"]9/8/2017
[/TD]
[TD="align: right"]9/9/2017
[/TD]
[TD="align: right"]9/10/2017
[/TD]
[TD="align: right"]9/11/2017
[/TD]
[TD="align: right"]9/12/2017
[/TD]
[TD="align: right"]9/13/2017
[/TD]
[TD="align: right"]9/14/2017
[/TD]
[TD="align: right"]9/15/2017
[/TD]
[/TR]
[TR]
[TD]Shoes
[/TD]
[TD]Sneakers, Nike
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Socks
[/TD]
[TD]Black, Ankle
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Pants
[/TD]
[TD]Khaki
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Shirts
[/TD]
[TD]Button Up
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Last 3 Days of Sales
[/TD]
[TD="colspan: 2"]Last 7 Days of Sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shirts
[/TD]
[TD]Button Up
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On the above example, I input daily sales as a new column each day. I would like to be able to determine what the last 3 days (as an example only) of sales were, and what the last 7 days of sales were for the specified item. I am open to any ideas!

Thanks in advance,
Mike

I would attach an example worksheet, but cannot find the attach button in the forums tools.

 

[Aladin Akyurek]

What are the expected results?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[TD]

Q​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]

9/1/2017​

[/TD]
[TD]

9/2/2017​

[/TD]
[TD]

9/3/2017​

[/TD]
[TD]

9/4/2017​

[/TD]
[TD]

9/5/2017​

[/TD]
[TD]

9/6/2017​

[/TD]
[TD]

9/7/2017​

[/TD]
[TD]

9/8/2017​

[/TD]
[TD]

9/9/2017​

[/TD]
[TD]

9/10/2017​

[/TD]
[TD]

9/11/2017​

[/TD]
[TD]

9/12/2017​

[/TD]
[TD]

9/13/2017​

[/TD]
[TD]

9/14/2017​

[/TD]
[TD]

9/15/2017​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Shoes[/TD]
[TD]Sneakers, Nike[/TD]
[TD]

1​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

14​

[/TD]
[TD]

6​

[/TD]
[TD]

3​

[/TD]
[TD]

8​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Socks[/TD]
[TD]Black, Ankle[/TD]
[TD]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Pants[/TD]
[TD]Khaki[/TD]
[TD]

9​

[/TD]
[TD]

12​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Shirts[/TD]
[TD]Button Up[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]last 3 day[/TD]
[TD]last 7 day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Shoes[/TD]
[TD]Sneakers, Nike[/TD]
[TD]

1​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Socks[/TD]
[TD]Black, Ankle[/TD]
[TD]

6​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Pants[/TD]
[TD]Khaki[/TD]
[TD]

5​

[/TD]
[TD]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Shirts[/TD]
[TD]Button Up[/TD]
[TD]

17​

[/TD]
[TD]

32​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


c9=SUMPRODUCT(($A$2:$A$5=$A9)*($B$2:$B$5=$B9)*($C$1:$Q$1>=MAX($C$1:$Q$1)-2)*($C$1:$Q$1<=MAX($C$1:$Q$1))*($C$2:$Q$5)) copy down

d9=SUMPRODUCT(($A$2:$A$5=$A9)*($B$2:$B$5=$B9)*($C$1:$Q$1>=MAX($C$1:$Q$1)-6)*($C$1:$Q$1<=MAX($C$1:$Q$1))*($C$2:$Q$5)) copy down

 

[mreynolds1775]

What are the expected results?

expected results in the example would be the average of the last 7 entries relating to shirts sold; 4.71

 

[mreynolds1775]

c9=SUMPRODUCT(($A$2:$A$5=$A9)*($B$2:$B$5=$B9)*($C$1:$Q$1>=MAX($C$1:$Q$1)-2)*($C$1:$Q$1<=MAX($C$1:$Q$1))*($C$2:$Q$5)) copy down

d9=SUMPRODUCT(($A$2:$A$5=$A9)*($B$2:$B$5=$B9)*($C$1:$Q$1>=MAX($C$1:$Q$1)-6)*($C$1:$Q$1<=MAX($C$1:$Q$1))*($C$2:$Q$5)) copy down

Great! Could I use this with a VLOOKUP? Assuming I would just type in the item name in cell C9?

 

[mreynolds1775]

In both cases, the result is 0...?

 

[MARZIOTULLIO]

hi
https://dropfile.to/bMq81VS

look this file

 

[Aladin Akyurek]

expected results in the example would be the average of the last 7 entries relating to shirts sold; 4.71

Control+shift+enter, not just enter:

SUM(OFFSET(INDEX(C1:XFD1,MATCH(9.99999999999999E+307,C1:XFD1)),MATCH($A8,IF($B$2:$B$5=$B8,$A$2:$A$5),0),0,1,-7))/7