Using Left or a Wildcard in Averageif

[smook]

Hi All,

Having some trouble identifying why this formula isn't working. Want to be able to get an average based of the day of the week. Was originally using the LEFT function in criteria range (ie MON, TUE etc) however swapped to a wildcard and still not returning a value.

=SUMIF(A:A,"Monday*",G:G)

Any help would be appreciated.


Cheers
Smook

 

[Armstrong_N14]

You can't use that formula since the Dates are interpreted as Numbers and your formula is for Text. Hence, a Helper column should help.

Hope this does the trick:

Book3
	A	B	C	D	E
1	Date	Day of the Week		Day of the Week	Count
2	Sunday, September 1, 2024	Sunday		Sunday	3
3	Monday, September 2, 2024	Monday		Monday	3
4	Tuesday, September 3, 2024	Tuesday		Tuesday	2
5	Wednesday, September 4, 2024	Wednesday		Wednesday	2
6	Thursday, September 5, 2024	Thursday		Thursday	2
7	Friday, September 6, 2024	Friday		Friday	2
8	Saturday, September 7, 2024	Saturday		Saturday	2
9	Sunday, September 8, 2024	Sunday
10	Monday, September 9, 2024	Monday
11	Tuesday, September 10, 2024	Tuesday
12	Wednesday, September 11, 2024	Wednesday
13	Thursday, September 12, 2024	Thursday
14	Friday, September 13, 2024	Friday
15	Saturday, September 14, 2024	Saturday
16	Sunday, September 15, 2024	Sunday
17	Monday, September 16, 2024	Monday
Sheet1

Cell Formulas
Range	Formula
B2:B17	B2	=TEXT(VALUE(WEEKDAY(A2,1)),"DDDD")
D2:D8	D2	=UNIQUE(B2:B17)
A3:A17	A3	=A2+1
E2:E8	E2	=COUNTIF(B2:B17,D2)
Dynamic array formulas.

 

[smook]

Thankyou so much. You lean something new etc etc

 

[Eric W]

You can do it without a helper column if you use FILTER:

Book1
	A	B	C	D	E	F
1	Date	Total		Day of the Week	Count	Average
2	Sunday, September 1, 2024	0		Sunday	3	86.33333
3	Monday, September 2, 2024	245		Monday	3	153
4	Tuesday, September 3, 2024	176		Tuesday	2	88
5	Wednesday, September 4, 2024	185		Wednesday	2	92.5
6	Thursday, September 5, 2024	201		Thursday	2	150
7	Friday, September 6, 2024	79		Friday	2	96.5
8	Saturday, September 7, 2024	253		Saturday	2	204
9	Sunday, September 8, 2024	179
10	Monday, September 9, 2024	82
11	Tuesday, September 10, 2024	0
12	Wednesday, September 11, 2024	0
13	Thursday, September 12, 2024	99
14	Friday, September 13, 2024	114
15	Saturday, September 14, 2024	155
16	Sunday, September 15, 2024	80
17	Monday, September 16, 2024	132
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=ROWS(FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2))
F2:F8	F2	=LET(f,FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2),SUM(f)/ROWS(f))
A3:A17	A3	=A2+1

 

[Armstrong_N14]

Awesome! I will have to learn that LET Function. Thanks!

You can do it without a helper column if you use FILTER:

Book1
	A	B	C	D	E	F
1	Date	Total		Day of the Week	Count	Average
2	Sunday, September 1, 2024	0		Sunday	3	86.33333
3	Monday, September 2, 2024	245		Monday	3	153
4	Tuesday, September 3, 2024	176		Tuesday	2	88
5	Wednesday, September 4, 2024	185		Wednesday	2	92.5
6	Thursday, September 5, 2024	201		Thursday	2	150
7	Friday, September 6, 2024	79		Friday	2	96.5
8	Saturday, September 7, 2024	253		Saturday	2	204
9	Sunday, September 8, 2024	179
10	Monday, September 9, 2024	82
11	Tuesday, September 10, 2024	0
12	Wednesday, September 11, 2024	0
13	Thursday, September 12, 2024	99
14	Friday, September 13, 2024	114
15	Saturday, September 14, 2024	155
16	Sunday, September 15, 2024	80
17	Monday, September 16, 2024	132
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=ROWS(FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2))
F2:F8	F2	=LET(f,FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2),SUM(f)/ROWS(f))
A3:A17	A3	=A2+1

 

[Phuoc]

Or try this in F2:

=AVERAGE(FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2))