can a COUNTIFS criteria be conditional?? (long post.....)

[dallenk]

PS.. I tried really hard to create a small sheet to upload but failed.


I am struggling with something, and now wondering if it's even possible to do.
I have a very complex spreadsheet that tracks attendance and payroll for a Paid-on-Call fire department.
members are volunteer and not paid until passing a probationary period.

members are tracked/paid by eligible events such as:

cannot respond yet (not counted as an eligible event)
can respond as unpaid ( counted as eligible event)
can respond as paid ( counted as eligible event)


where I am struggling is counting only eligible events after someone ceases to be a member.
In a nutshell I need to another criteria ONLY if there is a date entered in MASTER!S7,


MASTER sheet

MASTER!P7 : START DATE (yyyy-mm-dd)
MASTER!Q7 : RESPOND START DATE (yyyy-mm-dd)
MASTER!R7 : PAID RESPOND START DATE (yyyy-mm-dd)
MASTER!S7 : END DATE (yyyy-mm-dd)

here is a screenshot of the monthly sheet. Conditional formatting greys out boxes the member isn't eligible for perfectly.

$D$1:$H$1 = contains a hidden date (yyyy-mm-dd) created by data from other cells.

current formula in AN8 and filled down:

=IF(ISBLANK(MASTER!C7),"",COUNTIFS($D$1:$H$1,">="&MASTER!R7,$D$4:$H$4,"<>",$D8:$H8,"<>DP"))

which translates to countifs: eventdate is >= start date AND eventdate < end date AND member isn't deployed

"user, four" resigned/retired on the 28th so the box greys out correctly, but still counted as an "eligible training" day..


if I update the formula, and enter an end date of 2022-03-28 for "user, four"

=IF(ISBLANK(MASTER!C7),"",COUNTIFS($D$1:$H$1,">="&MASTER!R7,$D$1:$H$1,"<"&MASTER!S7,$D$4:$H$4,"<>",$D8:$H8,"<>DP"))

current active members: 0 events eligible as there is no date in the "end date" (it's counting exactly what I'm asking it to do...)
retired/resigned member: 3 eligible events counted correctly

Can a criteria be conditional? ie: if there is data in MASTER!S7, then use this criteria??
Or a better way of doing what I need. I am trying hard not to use an if(master!s7="",do one formula, do a different formula.. I have 12 sheets with hundreds of fields.. I'm trying to optimize things..


Thanks..

PS.. I tried really hard to create a small sheet to upload but failed.

 

[dallenk]

one mistake I just caught..

=IF(ISBLANK(MASTER!C7),"",COUNTIFS($D$1:$H$1,">="&MASTER!R7,$D$4:$H$4,"<>",$D8:$H8,"<>DP"))

which translates to countifs: eventdate is >= start date AND eventname isn't blank AND member isn't deployed

 

[dallenk]

bump?