Ignore blanks for a SUM

[Rex987456]

Trying to get the formula to ignore any blank results in columns B thru I for in the various days to get a proper average return. Can't figure out how to incorporate <>"" to make it work.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
33	2024									Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
34	Interval Start Time	M-F	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	8/29/2024	8/30/2024	8/31/2024	9/1/2024	9/2/2024	9/3/2024	9/4/2024	9/5/2024	9/6/2024	9/7/2024	9/8/2024	9/9/2024	9/10/2024	9/11/2024	9/12/2024	9/13/2024	9/14/2024	9/15/2024	9/16/2024	9/17/2024	9/18/2024	#########	9/20/2024	#########
35		Average	Average	Average	Average	Average	Average	Average	Average
36	1200-0100	59.8	83.3	55.6	33.3	33.3	91.7	76.7	83.3		100	67	100	75	67	50		100	100	100	100	100		100	67	40	50	75		50	33	100	100
37	0100-0200	56.9	66.7	33.3	33.3	54.2	87.5	50.0	66.7	67	100	100	100	100	100	100	50	100	100	100	100	0		100	100						0	50
38	0200-0300	50.5	66.7	100.0	33.3	8.3	56.3	87.5	33.3	0	75	100	0	100	100	100	33	50	50	50	100	100			100	100	50		100				100
39	0300-0400	29.4	50.0	16.7	33.3	50.0	0.0	75.0	0.0	100		100		50	50				100					100		100		100		100
40	0400-0500	36.3	16.7	41.7	33.3	60.4	25.0	75.0	33.3	75		100		50	25	50			100	0			0	67	100		100		100	50	100		100
41	0500-0600	69.1	66.7	66.7	91.7	56.3	68.8	75.0	33.3	100		100		100	100	100	100	100	100	100	100	100	100	25	100	100				75		75
42	0600-0700	64.7	33.3	83.3	100.0	37.5	75.0	55.0	80.6		100	100	100	100	100	100	100	100	20	67		50	100		0	100	75		100	100	50	100
43	0700-0800	74.7	66.7	77.4	86.1	78.6	66.3	58.8	66.7	91	50	75	50	100	88	58	60	46	60	50	100	91	100	100	69	40	100		54	100	64	100	60
44	0800-0900	92.5	88.1	91.7	100.0	92.5	90.9	70.1	43.3	100	64	55	80	100	100	100	80	100	77	38	100	75	100	90	100	62	13	64	100	100	100	100	88
45	0900-1000	76.6	77.4	100.0	57.2	70.0	79.7	13.3	10.1	100	86	2	15	85	100	66	67	75	17	3	73	100	86	33	88	18	13	75	100	20	80	71	16
46	1000-1100	76.5	74.7	85.7	64.8	77.7	78.5	39.4	41.5	100	89	30	67	77	100	44	88	25	41	22	79	100	100	33	100	10	36	69	57	50	90	100	77
47	1100-1200	70.8	91.9	41.4	66.2	54.2	96.9	33.9	72.4	100	88	15	100	86	14	27	17	100	46	32	100	10	100	0	100	8	86	90	100	71	100	100	67
48	1200-1300	63.2	81.1	83.1	18.0	42.9	89.2	37.7	81.1	82	70	5	89	62	100	9	0	100	44	55	82	91	31	23	100	20	100	100	58	14	67	87	82
49	1300-1400	64.6	90.9	78.5	27.2	49.5	77.5	69.9	69.6	22	60	60	89	91	80	44	50	100	54	60	82	67	30	86	100	89	60	100	89	7	40	50	77
50	1400-1500	64.7	74.3	81.4	24.1	65.3	74.8	61.5	75.1	39	33	33	86	93	75	25	57	86	31	54	83	69	31	78	80	82	86	46	100	17	88	100	100
51	1500-1600	69.3	73.0	67.8	47.6	68.5	84.7	88.2	95.2	67	50	53	100	77	33	55	36	100	100	100	83	100	80	71	89	100	86	59	70	8	100	100	100
52	1600-1700	70.8	87.9	70.4	82.6	55.8	64.6	98.1	93.3	100	67	92	89	91	22	78	0	82	100	91	80	100	100	36	17	100	100	93	89	70	88	93	100
53	1700-1800	47.8	54.0	34.9	68.0	45.3	40.2	97.9	96.7	76	65	92	90	80	19	59	38	9	100	100	27	47	79	42	32	100	100	55	39	67	25	55	100
54	1800-1900	50.7	63.8	50.8	58.8	42.1	43.3	67.9	95.2	68	25	30	100	73	100	20	50	89	42	86	19	21	63	20	46	100	100	100	31	94	30	13	100
55	1900-2000	48.6	59.3	54.8	32.6	58.5	38.3	59.9	86.7	71	50	33	100	50	47	40	100	24	100	100	50	18	50	33	46	63	60	78	100	8	29	33	44
56	2000-2100	58.9	43.8	43.6	54.0	80.5	63.8	81.3	70.1	60	80	38	53	78	6	46	82	44	88	82	44	25	56	100	56	100	75	10	100	60	80	75	100
57	2100-2200	70.9	62.3	87.3	47.2	82.5	71.2	51.5	82.6	92	73	9	75	29	82	0	90	67	40	73	73	80	42	60	90	57	100	86	100	100	88	56	100
58	2200-2300	85.3	73.6	100.0	95.2	94.2	66.7	87.5	100.0	77	14	50	100	38	100	86	100	100	100	100	100	100	100	100	86	100	100	83	100	100	100	67	100
59	2300-2400	94.1	91.7	100.0	91.7	100.0	87.5	70.0	66.7	100	50	100	100	100	100	75	100	100	100		100	100	100	100	100	80	100	75	100	100	100	100
60										76	59	35	71	75	55	31	54	60	53	53	66	63	67	48	70	48	64	69	74	49	61	72	73
Sheet1

Cell Formulas
Range	Formula
J33:AG33	J33	=TEXT(J34, "dddd")
B36:B59	B36	=SUM((WEEKDAY($J$34:$EI$34,2)<6)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)<6)*($J$34:$EI$34<>"")))
C36:C59	C36	=SUM((WEEKDAY($J$34:$EI$34,2)=1)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=1)*($J$34:$EI$34<>"")))
D36:D59	D36	=SUM((WEEKDAY($J$34:$EI$34,2)=2)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=2)*($J$34:$EI$34<>"")))
E36:E59	E36	=SUM((WEEKDAY($J$34:$EI$34,2)=3)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=3)*($J$34:$EI$34<>"")))
F36:F59	F36	=SUM((WEEKDAY($J$34:$EI$34,2)=4)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=4)*($J$34:$EI$34<>"")))
G36:G59	G36	=SUM((WEEKDAY($J$34:$EI$34,2)=5)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=5)*($J$34:$EI$34<>"")))
H36:H59	H36	=SUM((WEEKDAY($J$34:$EI$34,2)=6)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=6)*($J$34:$EI$34<>"")))
I36:I59	I36	=SUM((WEEKDAY($J$34:$EI$34,2)=7)*($J36:$EI36)/SUM(1*(WEEKDAY($J$34:$EI$34,2)=7)*($J$34:$EI$34<>"")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J60:AG60	Expression	=WEEKDAY(J$2:EI$2,2)>5	text	NO
J36:AF59	Expression	=WEEKDAY(J$2:EI$2,2)>5	text	NO
J33:AG35	Expression	=WEEKDAY(J$2:EI$2,2)>5	text	NO
J33:AG59	Expression	=WEEKDAY(J$2:EI$2,2)>5	text	NO

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H	I
33	2024
34	Interval Start Time	M-F	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
35		Average	Average	Average	Average	Average	Average	Average	Average
36	1200-0100	78.2	83.3	83.3	50.0	66.7	91.7	76.7	83.3
37	0100-0200	74.4	100.0	50.0	100.0	54.2	87.5	100.0	100.0
38	0200-0300	78.0	100.0	100.0	100.0	16.7	75.0	87.5	33.3
39	0300-0400	83.3	75.0	50.0	100.0	100.0	#DIV/0!	100.0	#DIV/0!
40	0400-0500	61.7	50.0	62.5	33.3	80.6	100.0	100.0	50.0
41	0500-0600	90.4	100.0	100.0	91.7	75.0	91.7	100.0	100.0
42	0600-0700	84.6	100.0	83.3	100.0	75.0	75.0	73.3	80.6
43	0700-0800	79.4	100.0	77.4	86.1	78.6	66.3	58.8	66.7
44	0800-0900	92.5	88.1	91.7	100.0	92.5	90.9	70.1	43.3
45	0900-1000	76.6	77.4	100.0	57.2	70.0	79.7	13.3	10.1
46	1000-1100	76.5	74.7	85.7	64.8	77.7	78.5	39.4	41.5
47	1100-1200	70.8	91.9	41.4	66.2	54.2	96.9	33.9	72.4
48	1200-1300	63.2	81.1	83.1	18.0	42.9	89.2	37.7	81.1
49	1300-1400	64.6	90.9	78.5	27.2	49.5	77.5	69.9	69.6
50	1400-1500	64.7	74.3	81.4	24.1	65.3	74.8	61.5	75.1
51	1500-1600	69.3	73.0	67.8	47.6	68.5	84.7	88.2	95.2
52	1600-1700	70.8	87.9	70.4	82.6	55.8	64.6	98.1	93.3
53	1700-1800	47.8	54.0	34.9	68.0	45.3	40.2	97.9	96.7
54	1800-1900	50.7	63.8	50.8	58.8	42.1	43.3	67.9	95.2
55	1900-2000	48.6	59.3	54.8	32.6	58.5	38.3	59.9	86.7
56	2000-2100	58.9	43.8	43.6	54.0	80.5	63.8	81.3	70.1
57	2100-2200	70.9	62.3	87.3	47.2	82.5	71.2	51.5	82.6
58	2200-2300	85.3	73.6	100.0	95.2	94.2	66.7	87.5	100.0
59	2300-2400	94.1	91.7	100.0	91.7	100.0	87.5	93.3	100.0
Sheet1

Cell Formulas
Range	Formula
B36:B59	B36	=AVERAGEIFS($J36:$AG36,$J$33:$AG$33,"<>Saturday",$J$33:$AG$33,"<>Sunday")
C36:I59	C36	=AVERAGEIFS($J36:$AG36,$J$33:$AG$33,C$34)