Using Left or a Wildcard in Averageif

S

smook

New Member
Joined
Sep 12, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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)

1726192851332.png


Any help would be appreciated.


Cheers
Smook
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
ABCDE
1DateDay of the WeekDay of the WeekCount
2Sunday, September 1, 2024SundaySunday3
3Monday, September 2, 2024MondayMonday3
4Tuesday, September 3, 2024TuesdayTuesday2
5Wednesday, September 4, 2024WednesdayWednesday2
6Thursday, September 5, 2024ThursdayThursday2
7Friday, September 6, 2024FridayFriday2
8Saturday, September 7, 2024SaturdaySaturday2
9Sunday, September 8, 2024Sunday
10Monday, September 9, 2024Monday
11Tuesday, September 10, 2024Tuesday
12Wednesday, September 11, 2024Wednesday
13Thursday, September 12, 2024Thursday
14Friday, September 13, 2024Friday
15Saturday, September 14, 2024Saturday
16Sunday, September 15, 2024Sunday
17Monday, September 16, 2024Monday
Sheet1
Cell Formulas
RangeFormula
B2:B17B2=TEXT(VALUE(WEEKDAY(A2,1)),"DDDD")
D2:D8D2=UNIQUE(B2:B17)
A3:A17A3=A2+1
E2:E8E2=COUNTIF(B2:B17,D2)
Dynamic array formulas.
 
Upvote 0
You can do it without a helper column if you use FILTER:

Book1
ABCDEF
1DateTotalDay of the WeekCountAverage
2Sunday, September 1, 20240Sunday386.33333
3Monday, September 2, 2024245Monday3153
4Tuesday, September 3, 2024176Tuesday288
5Wednesday, September 4, 2024185Wednesday292.5
6Thursday, September 5, 2024201Thursday2150
7Friday, September 6, 202479Friday296.5
8Saturday, September 7, 2024253Saturday2204
9Sunday, September 8, 2024179
10Monday, September 9, 202482
11Tuesday, September 10, 20240
12Wednesday, September 11, 20240
13Thursday, September 12, 202499
14Friday, September 13, 2024114
15Saturday, September 14, 2024155
16Sunday, September 15, 202480
17Monday, September 16, 2024132
Sheet2
Cell Formulas
RangeFormula
E2:E8E2=ROWS(FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2))
F2:F8F2=LET(f,FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2),SUM(f)/ROWS(f))
A3:A17A3=A2+1
 
Upvote 0
Awesome! I will have to learn that LET Function. Thanks!
Eric W said:
You can do it without a helper column if you use FILTER:

Book1
ABCDEF
1DateTotalDay of the WeekCountAverage
2Sunday, September 1, 20240Sunday386.33333
3Monday, September 2, 2024245Monday3153
4Tuesday, September 3, 2024176Tuesday288
5Wednesday, September 4, 2024185Wednesday292.5
6Thursday, September 5, 2024201Thursday2150
7Friday, September 6, 202479Friday296.5
8Saturday, September 7, 2024253Saturday2204
9Sunday, September 8, 2024179
10Monday, September 9, 202482
11Tuesday, September 10, 20240
12Wednesday, September 11, 20240
13Thursday, September 12, 202499
14Friday, September 13, 2024114
15Saturday, September 14, 2024155
16Sunday, September 15, 202480
17Monday, September 16, 2024132
Sheet2
Cell Formulas
RangeFormula
E2:E8E2=ROWS(FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2))
F2:F8F2=LET(f,FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2),SUM(f)/ROWS(f))
A3:A17A3=A2+1
Click to expand...
 
Upvote 0
Or try this in F2:

=AVERAGE(FILTER($B$2:$B$17,TEXT($A$2:$A$17,"dddd")=D2))
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
1K
KRice
K
D
  • Solved
AVERAGEIF Returning Wrong Average Value
Replies
4
Views
967
6StringJazzer
6StringJazzer
J
  • Question Question
Complicated (for me) countifs function
Replies
5
Views
444
Fluff
Fluff
K
  • Question Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
1K
arthurbr
arthurbr
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
74
Fluff
Fluff

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top