Sumproduct and Hlookup?

[throtmorton]

Hi folks,

I have a launch schedule in the top section. What I need to do is cycle through the Core Revenue Section, and then add in the Total Revenue section the appropriate revenues. In this example I show only three months and one revenue item, so it's easy touse the formulas I've shown, but I have 60 months, and around 100 line items. Is there a way *without VBA* so that for example in cell D12, it knows to what equates to the laborious formula I've shown?

Thanks!
Paul

I have a

 

[Peter_SSs]

I suggest that you ...

1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).
   
   
2. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
   
   
3. Describe clearly what your ultimate goal is.

IF you have Excel 365 with the new dynamic array functions and your goal is to produce those numbers in row 12 then ..

20 08 04.xlsm
	A	B	C	D
1
2		1	2	3
3			1	2
4
5
6
7		250	550	850
8
9
10
11
12		0	250	1050
Sumproduct

Cell Formulas
Range	Formula
B12:D12	B12	=SUMPRODUCT(($B3:B3)*(SORTBY($B7:B7,COLUMN($B7:B7),-1)))

 

[Peter_SSs]

IF you have Excel 365 with the new dynamic array functions ..

If not, try

20 08 04.xlsm
	A	B	C	D
1
2		1	2	3
3			1	2
4
5
6
7		250	550	850
8
9
10
11
12		0	250	1050
Sumproduct

Cell Formulas
Range	Formula
B12:D12	B12	=SUMPRODUCT(($B3:B3)*N(OFFSET(B7,0,COLUMN($B7)-COLUMN($B7:B7))))

 

[throtmorton]

Hi Peter,

Sorry for not providing more info as you say. I've updated my profile, and will look at XL2BB and frame questions better.

That said, thank you so much for still navigating to an elegant solution! It works perfectly.

Much obliged!

Cheers,
Paul

 

[Peter_SSs]

You're welcome.
.. & thanks for updating your profile.