moogthemoog
Board Regular
- Joined
- Nov 17, 2004
- Messages
- 51
I'm trying to extract separately the actual and budget (row 5) profit figures from row 7, but by department (row 4).
Because the department is merged across 3 columns (actual, budget, variance), I am not getting a valid return for the budget figures, which are in the 2nd column of each department, but the department is only shown in the 1st column (though merged)
I am struggling with SUMIFS and SUMPRODUCT. I suspect I will need INDEX, MATCH, or OFFSET in conjunction with SUMPRODUCT, but I really am not sure.
Please can anyone help?
Many thanks in anticipation...
Jon
Because the department is merged across 3 columns (actual, budget, variance), I am not getting a valid return for the budget figures, which are in the 2nd column of each department, but the department is only shown in the 1st column (though merged)
I am struggling with SUMIFS and SUMPRODUCT. I suspect I will need INDEX, MATCH, or OFFSET in conjunction with SUMPRODUCT, but I really am not sure.
Please can anyone help?
Many thanks in anticipation...
Jon
TB extract.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Actual v Budget | ||||||||||||||
2 | Aug 2020 | ||||||||||||||
3 | |||||||||||||||
4 | Financial Row | Administration - AL | Administration - GG | Concrete | Laboratory | ||||||||||
5 | Actual | Budget | Var To Act | Actual | Budget | Var To Act | Actual | Budget | Var To Act | Actual | Budget | Var To Act | |||
6 | Total - Sales | £0.00 | £0.00 | £0.00 | £2.87 | £0.13 | £2.74 | (£95,526.23) | (£128,436.89) | £32,910.66 | (£120.00) | (£1,153.24) | £1,033.24 | ||
7 | Net Profit/(Loss) | £2,849.50 | £252.89 | £2,596.61 | £79,428.76 | £74,790.44 | £4,638.32 | (£19,106.15) | (£14,112.05) | (£4,994.10) | £9,836.04 | £11,521.94 | (£1,685.90) | ||
8 | |||||||||||||||
9 | |||||||||||||||
10 | |||||||||||||||
11 | Net Profit | Actual | Budget | ||||||||||||
12 | Administration - AL | 2,849.50 | - | SUMIFS | |||||||||||
13 | Administration - GG | 79,428.76 | - | SUMIFS | |||||||||||
14 | Concrete | (19,106.15) | - | SUMPRODUCT | |||||||||||
15 | Laboratory | 9,836.04 | - | SUMPRODUCT | |||||||||||
TleCEBITDAActvBudProfitLo |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B12:B13 | B12 | =SUMIFS($7:$7,$4:$4,$A12,$5:$5,"Actual") |
C12:C13 | C12 | =SUMIFS($7:$7,$4:$4,$A12,$5:$5,"Budget") |
B14:B15 | B14 | =SUMPRODUCT(--($5:$5="Actual")*($4:$4=$A14),$7:$7) |
C14:C15 | C14 | =SUMPRODUCT(--($5:$5="Budget")*($4:$4=$A14),$7:$7) |