SUMIF with header criteria

[MGCC]

I've searched the internet far and wide for an answer to this question with no success. I'm hoping the brilliant minds in this forum can be of assistance.

	A	B	C	D	E	F
1​	Calc	YES	YES	NO	YES	NO
2​	#nights	3	3	2	3	2
3​	Month	A	B	C	D	E
4​	Jan	54		30		123
5​	Jan				75	123
6​	Jan		1877			123
7​	Feb		1877	30		123
8​	Feb		1877	30		123
9​	Mar	54		30		123
10​	Mar	54		30		123

I need a formula which will return the value based on multiple criteria.
E.g. I want to know the value per night (Row 2) for Jan (Column A) if header is YES (Row 1). In this case it should be (54+75+1877)/Count (3)/ Nights (3) = 222.83

Any help with this would be a greatly appreciated.

 

[Peter_SSs]

I want to know the value per night (Row 2) for Jan (Column A) if header is YES (Row 1). In this case it should be (54+75+1877)/Count (3)/ Nights (3) = 222.83

What would the calculation be if the #nights in the YES columns were not all the same?
For example, what would the value per night (Row 2) for Jan (Column A) if header is YES (Row 1) for the following sample data?

23 10 23.xlsm
	A	B	C	D	E	F
1	Calc	YES	YES	NO	YES	NO
2	#nights	3	3	2	10	2
3	Month	A	B	C	D	E
4	Jan	54		30		123
5	Jan				75	123
6	Jan		1877			123
7	Feb		1877	30		123
8	Feb		1877	30		123
9	Mar	54		30		123
10	Mar	54		30		123
MGCC

 

[MGCC]

Hi, If the value is YES, number of night is always the same. Thank you

 

[Peter_SSs]

Hi, If the value is YES, number of night is always the same. Thank you

See if this does what you want then.

23 10 23.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Calc	YES	YES	NO	YES	NO		Jan	YES	222.8889
2	#nights	3	3	2	3	2
3	Month	A	B	C	D	E
4	Jan	54		30		123
5	Jan				75	123
6	Jan		1877			123
7	Feb		1877	30		123
8	Feb		1877	30		123
9	Mar	54		30		123
10	Mar	54		30		123
MGCC

Cell Formulas
Range	Formula
J1	J1	=AVERAGE(FILTER(FILTER(B4:F10,B1:F1=I1),A4:A10=H1))/XLOOKUP(I1,B1:F1,B2:F2)

 

[MGCC]

Great. This works. Thank you so much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.