MurrayBarn
New Member
- Joined
- May 27, 2012
- Messages
- 32
- Office Version
- 365
- Platform
- Windows
Hi
Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.
I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.
=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum
FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3
<tbody>
</tbody>
The answer I am looking for is (17,727,087.38) ie the Bednight Sales for all four hotels for all three months. Any ideas?
Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.
I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.
=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum
FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3
A9 | B9 | C9 | D9 | E9 | F9 |
107000 | 10 | BEDNIGHT SALES Hotel1 | (1,903,410.46) | (3,442,107.88) | (3,321,870.88) |
107000 | 15 | BEDNIGHT SALES Hotel2 | (737,615.48) | (1,024,925.30) | (774,900.55) |
107000 | 20 | BEDNIGHT SALES Hotel3 | (1,349,673.99) | (1,887,209.76) | (881,464.42) |
107000 | 30 | BEDNIGHT SALES Hotel4 | (684,964.02) | (999,551.32) | (719,393.32) |
107005 | 70 | SALES CANCELLATION FEES RECEIV | 0.00 | 0.00 | (54,326.75) |
107010 | 10 | DISCOUNT Hotel1 | 18,572.03 | 10,046.20 | 10,441.81 |
107010 | 15 | DISCOUNT LITTLE Hotel2 | 9,991.11 | 4,320.99 | 513.00 |
107010 | 20 | DISCOUNT Hotel3 | 27,639.54 | 35,879.81 | 1,891.56 |
107010 | 30 | DISCOUNT Hotel4 | 20,479.29 | 364.80 | 6,515.19 |
<tbody>
</tbody>
The answer I am looking for is (17,727,087.38) ie the Bednight Sales for all four hotels for all three months. Any ideas?