I'm not sure if this is possible using Sumproduct but believe it should be possible generally, from the example below I'd like to sum values of n rows apart, but before they are summed are multiplied by a fixed value (the FX rate). It's this multiplication part I'm not sure how to do, the target values are below.
Hope that explains it clearly.
Thanks,
Paul
Hope that explains it clearly.
Thanks,
Paul
SumProduct Question.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
1 | Mar-20 | Apr-20 | |||
2 | |||||
3 | FX | 1.3 | 1.2 | ||
4 | Company 1 | ||||
5 | Revenue | 10 | 10 | ||
6 | COS | 6 | 6 | ||
7 | Profit | 4 | 4 | ||
8 | |||||
9 | FX | 1.4 | 1.6 | ||
10 | Company 2 | ||||
11 | Revenue | 12 | 12 | ||
12 | COS | 7 | 7 | ||
13 | Profit | 5 | 5 | ||
14 | |||||
15 | |||||
16 | Consolidation | ||||
17 | Revenue | 22 | 22 | ||
18 | COS | 13 | 13 | ||
19 | Profit | 9 | 9 | ||
20 | |||||
21 | |||||
22 | Target Version | ||||
23 | Revenue | 30 | 31 | ||
24 | COS | 18 | 18 | ||
25 | Profit | 12 | 13 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C7:D7,C13:D13 | C7 | =C5-C6 |
C17:D19 | C17 | =(SUMPRODUCT(--(MOD(ROW(C5:C13)-ROW(C5),6)=0),C5:C13)) |
C23:D25 | C23 | =(C5*C$3)+(C11*C$9) |