Summing values based on drop down menu selection

[RodneyC]

Thanks to the help from user RasGhul in another post, I was able to create this formula:

=IFNA(INDEX('Revenue Analysis'!$O$2:$O$1201,MATCH($D$4&$D7,INDEX('Revenue Analysis'!$B$2:$B$1201&'Revenue Analysis'!$C$2:$C$1201,),0)),"")

Now I need to a modification of it for another cell in column L that it will total $M$2:$M$1201 + $N$2:$N$1201 + $O$2:$O$1201 then divide that number by 3.

Thanks in advance

 

[RasGhul]

Are you able to post some data? Just to clarify are you trying to sum 3 lookup values then divide by 3?

sumproduct maybe easier

 

[RodneyC]

I can post some data in the AM, but the purpose of adding the three numbers, then dividing by three is to get the average. Wife is screaming at me to come eat. I'll try and add some data in the AM

 

[RodneyC]

The goal is to get an average month over a 3 month period.

This formula =IFNA(INDEX('Revenue Analysis'!$M$2:$M$1201,MATCH($D$4&$D7,INDEX('Revenue Analysis'!$B$2:$B$1201&'Revenue Analysis'!$C$2:$C$1201,),0)),"") produces the value 15,717.
This formula: =IFNA(INDEX('Revenue Analysis'!$N$2:$N$1201,MATCH($D$4&$D7,INDEX('Revenue Analysis'!$B$2:$B$1201&'Revenue Analysis'!$C$2:$C$1201,),0)),"") produces the value 23,619
This formula:=IFNA(INDEX('Revenue Analysis'!$O$2:$O$1201,MATCH($D$4&$D7,INDEX('Revenue Analysis'!$B$2:$B$1201&'Revenue Analysis'!$C$2:$C$1201,),0)),"") produces the value 16,111

Together they total 55,447. Divide that by 3 and I get 18482 for my average month. My original thought was to somehow combine those three formulas into one and produce the results that way. After sleeping on it, I think I'll have four individual columns, the first three with each of the formulas above and a forth that adds and divides the results from the first three, then I'll hide the first three.

RasGhul.... thanks for your help on this. There is one more formula on this project I'll likely need help with. I'll post a new thread when I'm ready. Appreciate your help.

 

[RasGhul]

Sumproduct may be easier as long as D4 & D7 Criteria combine to make a unique value

Multiple references for Drop Down formula equation_RodneyC.xlsx
	B	C										M	N	O
1	Name	MFR										Line Seq.	Order Date	Expected Delivery Date
2	ELTRON	3004961										10,000	44,502	44,502
3	ELTRON	3004954										10,000	44,525	44,525
4	ELTRON	3004967										10,000	44,525	44,525
5	ELTRON1	3009461										15,717	23,619	16,111
6	ELTRON	3009451										10,000	44,538	44,538
7	ELTRON	3009458										10,000	44,538	44,538
8	ELTRON	3009462										10,000	44,538	44,538
9	ELTRON	3010182										10,000	44,580	44,580
10	REECE PTY LTD - SA BURWOOD	3017532										10,000	44,580	44,580
Revenue Analysis

Multiple references for Drop Down formula equation_RodneyC.xlsx
	C	D
4	Criteria1	ELTRON1
5
6
7	Criteria2	3009461
8
9
10	SUMPRODUCT	18,482
Sheet2

Cell Formulas
Range	Formula
D10	D10	=SUMPRODUCT((('Revenue Analysis'!$B$2:$B$17=Sheet2!$D$4)*('Revenue Analysis'!$C$2:$C$17=Sheet2!$D$7)*'Revenue Analysis'!$M$2:$M$17)+(('Revenue Analysis'!$B$2:$B$17=Sheet2!$D$4)*('Revenue Analysis'!$C$2:$C$17=Sheet2!$D$7)*'Revenue Analysis'!$N$2:$N$17)+(('Revenue Analysis'!$B$2:$B$17=Sheet2!$D$4)*('Revenue Analysis'!$C$2:$C$17=Sheet2!$D$7)*'Revenue Analysis'!$O$2:$O$17))/3