Conditional sum or average from list of cells based on dependent dropdown values from different cells

[Mtalreja93]

Cell 1 : User can select Frequency: Monthly / Quarterly / Yearly
Cell 2: User can select Type of Function : Sum or Average
Cell 3-19: Have values for each Month, Quarter and Year

Now, How generate result applying condition that based on choice ( type of Frequency ) - excel picks only those values from Cell 3-19 and performs function that user has selected in Cell 2, either sum or average

S.No	Unit	Frequency	Q1	Q2	Q3	Q4	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12	Y	Result
1	SUM / Average	Yearly / Quarterly / Monthly	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

 

[ajitexcel]

Hello there, just add the following formula in Results Column

=IFS(AND(A2="Sum",B2="Monthly"),SUM(G2:R2),AND(A2="Sum",B2="Quarterly"),SUM(C2:F2),AND(A2="Sum",B2="Yearly"),S2,AND(A2="Average",B2="Monthly"),AVERAGE(G2:R2),AND(A2="Average",B2="Quarterly"),AVERAGE(C2:F2),AND(A2="Average",B2="Yearly"),AVERAGE(S2))

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this would suit you.

22 09 11.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Unit	Frequency	Q1	Q2	Q3	Q4	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12	Y	Result
2	Sum	Quarterly	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	4
3	Average	Monthly	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
4	Sum	Yearly	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5	Sum	Monthly	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	12
Sum or Average

Cell Formulas
Range	Formula
U2:U5	U2	=LET(r,FILTER(D2:T2,LEFT(D$1:T$1,1)=LEFT(C2,1)),IF(B2="Sum",SUM(r),AVERAGE(r)))

Cells with Data Validation
Cell	Allow	Criteria
B2:B5	List	Sum,Average
C2:C5	List	Yearly,Quarterly,Monthly