SumProduct Question

[smitpau]

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

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)

 

[Eric W]

Yes, offsetting the ranges in a SUMPRODUCT (or sometimes a SUMIF) is a useful trick to know. It took me a bit to think of it this time though. Incidentally, I could have made the formulas the same by putting $ signs on the B3:B9 range. More second thoughts. Anyway I'm glad it works for you.