Countifs Number Of Times Team Play on each weekday

C

CordingBags

New Member
Joined
Mar 7, 2022
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
I am trying to count the number of times each team plays on which weekday.
My issue is the "input" cell displays the weekday from an adjacent cell.
Therefore the number is different every time.
How do I get COUNTIFS to just "read" the displayed Weekday.

Cell D2 is =IF(F2<1,"",WEEKDAY(F2))
Cell D5 is =IF(F5<1,"",WEEKDAY(F5))
Cell D11 is =IF(F11<1,"",WEEKDAY(F11))
This continues down to ROW 189

I need to count the number of times a team plays on for instance MONDAY,

The fixtures are held columns H - N, some leagues have an odd number of teams therefore not every team will play on a particular fixture day.

Any Help Appreciated
Thanks
Paul

League Booking 2024-25.xlsm
DEFGHIJKLMN
1DayTimeDateR1R2R3R4R5R6R7
2Wed18:3002/10/2024L1L3L4L2
318:30L7L5L8L6
4DayTimeDateR1R2R3R4R5R6R7
5Tue18:3015/10/2024L7L1L3L2
618:30L8L6L4L5
7DayTimeDateR1R2R3R4R5R6R7
8Thu18:3024/10/2024L2L1L3L7
918:30L4L5L8L6
10DayTimeDateR1R2R3R4R5R6R7
11Mon18:3028/10/2024L7L6L1L2
1218:30L5L8L4L3
13DayTimeDateR1R2R3R4R5R6R7
14Fri18:3008/11/2024L2L1L6L7
1518:30L8L3L5L4
16DayTimeDateR1R2R3R4R5R6R7
17Wed18:3013/11/2024L5L7L2L6
1818:30L8L3L1L4
19DayTimeDateR1R2R3R4R5R6R7
20Wed18:3020/11/2024L7L5L6L1
2118:30L2L4L3L8
LADIES EVE
Cell Formulas
RangeFormula
D2,D20,D17,D14,D11,D8,D5D2=IF(F2<1,"",WEEKDAY(F2))
E3,E21,E18,E15,E12,E9,E6E3=IF(E2="","",E2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E20Expression=AND(F20<>"", E20="" )textYES
F20Expression=AND(E20<>"", F20="" )textNO
F20Expression=IF(AND(F20=0, F17>$G$1490),TRUE,FALSE)textNO
F20Expression=F17<1textNO
F20Cell Value<=F17textNO
E17Expression=AND(F17<>"", E17="" )textYES
F17Expression=AND(E17<>"", F17="" )textNO
F17Expression=IF(AND(F17=0, F14>$G$1490),TRUE,FALSE)textNO
F17Expression=F14<1textNO
F17Cell Value<=F14textNO
E14Expression=AND(F14<>"", E14="" )textYES
F14Expression=AND(E14<>"", F14="" )textNO
F14Expression=IF(AND(F14=0, F11>$G$1490),TRUE,FALSE)textNO
F14Expression=F11<1textNO
F14Cell Value<=F11textNO
E11Expression=AND(F11<>"", E11="" )textYES
F11Expression=AND(E11<>"", F11="" )textNO
F11Expression=IF(AND(F11=0, F8>$G$1490),TRUE,FALSE)textNO
F11Expression=F8<1textNO
F11Cell Value<=F8textNO
E8Expression=AND(F8<>"", E8="" )textYES
F8Expression=AND(E8<>"", F8="" )textNO
F8Expression=IF(AND(F8=0, F5>$G$1490),TRUE,FALSE)textNO
F8Expression=F5<1textNO
F8Cell Value<=F5textNO
E5Expression=AND(F5<>"", E5="" )textYES
J17:K18,M17:N18,H17:H18Cell ValueduplicatestextYES
H14:J15,L14:N15Cell ValueduplicatestextYES
H11:I12,K11:N12Cell ValueduplicatestextYES
H6:N6Expression=IF(AND(LEN(H5)>0,LEN(H6)=0),TRUE, FALSE)textNO
H5:N5Expression=IF(AND(LEN(H6)>0,LEN(H5)=0),TRUE, FALSE)textNO
H5:N6Cell ValueduplicatestextYES
AK3:AQ3,K9,M9:N9,H12:I12,K12:N12,H15:J15,L15:N15,H18,J18:K18,M18:N18,H21:K21,M21,M24:N24,H24:K24,M30:N30,H30:J30,H33:L33,N33,H36:L36,N36,M39:N39,H39:K39,H42:I42,K42:N42,H45:I45,K45:N45,M48:N48,H48:K48,H51,J51:N51,H54,J54:N54,L57:N57,H57:J57,H60:L60,N60Expression=IF(AND(LEN(H2)>0,LEN(H3)=0),TRUE, FALSE)textNO
AK2:AQ2,K8,M8:N8,H11:I11,K11:N11,H14:J14,L14:N14,H17,J17:K17,M17:N17,H20:K20,M20,M23:N23,H23:K23,M29:N29,H29:J29,H32:L32,N32,H35:L35,N35,M38:N38,H38:K38,H41:I41,K41:N41,H44:I44,K44:N44,M47:N47,H47:K47,H50,J50:N50,H53,J53:N53,L56:N56,H56:J56,H59:L59,N59Expression=IF(AND(LEN(H3)>0,LEN(H2)=0),TRUE, FALSE)textNO
F2Expression=AND(E2<>"", F2="" )textNO
F2Expression=IF(AND(F2=0, F1048575>$G$1490),TRUE,FALSE)textNO
F2Expression=F1048575<1textNO
F2Cell Value<=F1048575textNO
F5Expression=AND(E5<>"", F5="" )textNO
F5Expression=IF(AND(F5=0, F2>$G$1490),TRUE,FALSE)textNO
F5Expression=F2<1textNO
F5Cell Value<=F2textNO
F21Expression=NOT(ISBLANK(F21))textYES
F18Expression=NOT(ISBLANK(F18))textYES
F15Expression=NOT(ISBLANK(F15))textYES
F12Expression=NOT(ISBLANK(F12))textYES
F9Expression=NOT(ISBLANK(F9))textYES
F6Expression=NOT(ISBLANK(F6))textYES
F3Expression=NOT(ISBLANK(F3))textYES
E2Expression=AND(F2<>"", E2="" )textYES
E21Expression=E21<>E20textYES
E21Expression=E20textYES
E18Expression=E18<>E17textYES
E18Expression=E17textYES
E15Expression=E15<>E14textYES
E15Expression=E14textYES
E12Expression=E12<>E11textYES
E12Expression=E11textYES
E9Expression=E9<>E8textYES
E9Expression=E8textYES
E6Expression=E6<>E5textYES
E6Expression=E5textYES
E3Expression=E3<>E2textYES
E3Expression=E2textYES
D20Cell Value=1textYES
D20Cell Value=7textYES
D17Cell Value=1textYES
D17Cell Value=7textYES
D14Cell Value=1textYES
D14Cell Value=7textYES
D11Cell Value=1textYES
D11Cell Value=7textYES
D8Cell Value=1textYES
D8Cell Value=7textYES
D5Cell Value=1textYES
D5Cell Value=7textYES
D21Expression=NOT(ISBLANK(D21))textYES
D18Expression=NOT(ISBLANK(D18))textYES
D15Expression=NOT(ISBLANK(D15))textYES
D12Expression=NOT(ISBLANK(D12))textYES
D9Expression=NOT(ISBLANK(D9))textYES
D6Expression=NOT(ISBLANK(D6))textYES
D3Expression=NOT(ISBLANK(D3))textYES
D2Cell Value=1textYES
D2Cell Value=7textYES
M20:M21,H20:K21Cell ValueduplicatestextYES
K8:K9,M8:N9,H8:H9Cell ValueduplicatestextYES
K2:N3,H2:I3Cell ValueduplicatestextYES
Cells with Data Validation
CellAllowCriteria
H5:N6List=$Q$3:$Q$16
K8:K9List=$Q$3:$Q$16
M8:N9List=$Q$3:$Q$16
H11:I12List=$Q$3:$Q$16
K11:N12List=$Q$3:$Q$16
H14:J15List=$Q$3:$Q$16
L14:N15List=$Q$3:$Q$16
H17:H18List=$Q$3:$Q$16
J17:K18List=$Q$3:$Q$16
M17:N18List=$Q$3:$Q$16
H20:K21List=$Q$3:$Q$16
M20:M21List=$Q$3:$Q$16
H8:H9List=$Q$3:$Q$16
H2:I3List=$Q$3:$Q$16
K2:N3List=$Q$3:$Q$16
E1Timebetween 00:01:00 and 23:59:00
F7:F8Datebetween 01/01/2023 and 31/12/2100
F13:F14Datebetween 01/01/2023 and 31/12/2100
F10:F11Datebetween 01/01/2023 and 31/12/2100
F16:F17Datebetween 01/01/2023 and 31/12/2100
F19:F20Datebetween 01/01/2023 and 31/12/2100
E3List=E2
E6List=E5
E7Timebetween 00:01:00 and 23:59:00
E9List=E8
E10Timebetween 00:01:00 and 23:59:00
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Like this perhaps?

ABCDEFGHIJKLMN
1TESTDayTimeDateR1R2R3R4R5R6R7
2TeamL4Wed18:302 Oct 2024L1L3L4L2
3DayWedL7L5L8L6
4Count3DayTimeDateR1R2R3R4R5R6R7
5Tue18:3015 Oct 2024L7L1L3L2
6L8L6L4L5
7DayTimeDateR1R2R3R4R5R6R7
8Thu18:3024 Oct 2024L2L1L3L7
9L4L5L8L6
10DayTimeDateR1R2R3R4R5R6R7
11Mon18:3028 Oct 2024L7L6L1L2
12L5L8L4L3
13DayTimeDateR1R2R3R4R5R6R7
14Fri18:308 Nov 2024L2L1L6L7
15L8L3L5L4
16DayTimeDateR1R2R3R4R5R6R7
17Wed18:3013 Nov 2024L5L7L2L6
18L8L3L1L4
19DayTimeDateR1R2R3R4R5R6R7
20Wed18:3020 Nov 2024L7L5L6L1
21L2L4L3L8
Sheet1
Cell Formulas
RangeFormula
D2,D20,D17,D14,D11,D8,D5D2=IF(F2<1,"",WEEKDAY(F2))
B4B4=SUM((INDEX(D1:D21,2+3*INT((ROW(D1:D21)-ROW(D1))/3))=B3)*(H1:N21=B2))
 
Upvote 0
Solution
Works great thanks, I would never have stumbled over such a formula.

Again Many Thanks
Cheers

Paul
 
Upvote 0
Hi Stephen

I have uploaded a follow up question as although this works great in the original XLSM file, (many thanks) it is just "ignored" in the corresponding XLS file.
Unfortunately I have to use XLS for compatibility :(
Only difference is the XLSM file is saved using the alternative file format XLS.
Any help suggestion appreciated.

Thanks

Paul
PS Alternative is to get to final edit in XLSM and then save to XLS for output.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Formula to identify date and location from a table and return value
Replies
3
Views
35
Fluff
Fluff
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
199
Pbobas
P
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
gtd526
  • Question Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
305
gtd526
gtd526
gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
329
gtd526
gtd526

Forum statistics

Threads
1,223,943
Messages
6,175,546
Members
452,652
Latest member
eduedu

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