I have data that is entered daily, and summed weekly. As the week progresses I need to sum the 3 previous days (excluding the total column).
1) I am having trouble determining how to find the range using an HLookup of the reference. (Ex: if Ref = 8 then sum Thu-Sat for item 1)
2) I have no clue to go about finding the range when the total is in the middle. (Ex: if Ref=11 then sum Sat-Sun, but leave out the total)
Any help would be greatly appreciated. (using Excel 2010)
Excel 2010
1) I am having trouble determining how to find the range using an HLookup of the reference. (Ex: if Ref = 8 then sum Thu-Sat for item 1)
2) I have no clue to go about finding the range when the total is in the middle. (Ex: if Ref=11 then sum Sat-Sun, but leave out the total)
Any help would be greatly appreciated. (using Excel 2010)
Excel Workbook | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||
2 | Thu | Fri | Sat | Sun | TOTAL | Mon | Tue | Wed | |||
3 | Item 1 | 16 | 18 | 22 | 14 | 127 | 20 | 18 | 15 | ||
4 | Item 2 | 14 | 12 | 20 | 22 | 72 | 8 | 12 | 16 | ||
5 | |||||||||||
6 | |||||||||||
7 | |||||||||||
8 | Ref | 8 | 11 | ||||||||
9 | DOW | Sun | Tue | ||||||||
10 | Item 1 | 56 | 56 | ||||||||
11 | Item 2 | 46 | 50 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3 | =SUM(G3:M3) | |
F4 | =SUM(G4:M4) | |
B9 | =HLOOKUP(B8,$A$1:$V$4,2,FALSE) | |
B10 | =SUM(B3:D3) | |
B11 | =SUM(B4:D4) | |
C9 | =HLOOKUP(C8,$A$1:$V$4,2,FALSE) | |
C10 | =SUM(G3,D3:E3) | |
C11 | =SUM(G4,D4:E4) |