New player sign on team start 13/01/2020 should show up as blank instead say miss 7 match

[micope21]

Hi all,

You see highlight yellow in sample file?
Cell G6 is Start Date of the season.
Cell C9 How many Match play.
Row A15:A24 How many match players play
Row G15:G24 How many time Player not turn up miss the match.
Column M23:AD23 My problem is he sign on as new players start 13/01/2020 show up Miiss cell G23 say 7 Match?
It should be blank? How to solve this problem to make is a blank or 0

All details explain same as above are in Sample team file highlight yellow cell, row and column.

Regards
micope21

[Bo_Ry]

Please try at G15

=IF($L15="","",COUNTIFS($M$9:$AD$9,">=0",$M$6:$AD$6,">="&IF(AE15="",0,RIGHT($AE15,10)))-$A15)

You need to add Sign on date at AE

[micope21]

Please try at G15

=IF($L15="","",COUNTIFS($M$9:$AD$9,">=0",$M$6:$AD$6,">="&IF(AE15="",0,RIGHT($AE15,10)))-$A15)

You need to add Sign on date at AE

Thank you Bo_By.

Yes agreed put in new signing on Date.

Thank for formula row A

Please Login or Register  to view this content.

I'm still using this formulas in cell D15 W

Please Login or Register  to view this content.

Other cell E15 L

Please Login or Register  to view this content.

It there better way formulas like you did row A?

If I don't hear you about this formula in next few hours?
I will mark as solve?

[Bo_Ry]

Please try at
D15
=SUMPRODUCT(--ISNUMBER(FIND({"W";"WW"},SUBSTITUTE(M15:AD15,8,"W"))))

E15
=SUM(COUNTIF(M15:AD15,{"*L*","LL"}))

[micope21]

Please try at
D15
=SUMPRODUCT(--ISNUMBER(FIND({"W";"WW"},SUBSTITUTE(M15:AD15,8,"W"))))

E15
=SUM(COUNTIF(M15:AD15,{"*L*","LL"}))

Big Thank you Bo_By, Excellent formulas. I notice it speed up lot better.

Add Rep and make a solve.

Thank you again
Cheer