COUNTIF Formula String

[Radiatedjay2]

The formula string below currently counts the number of supervisors scheduled to work based on specific entries in three cells. Certain entries add to the count and certain entries subtract from the count.

=COUNTIF(KI4:KI6,"*N*")+COUNTIF(KI4:KI6,"PHY")+COUNTIF(KI4:KI6,"TRD")+COUNTIF(KI4:KI6,"MT")+COUNTIF(KI4:KI6,"*D*")-COUNTIF(KI4:KI6,"DC")-COUNTIF(KI4:KI6,"XD")-COUNTIF(KI4:KI6,"XN")

What I need it to do is subtract 1 from the total if the total is => 3. How would I modify this formula to accomplish that.

 

[6StringJazzer]

What version of Excel are you using? (Good idea to update your user profile to show this.) It will make a big difference in what solution to give you.

 

[bebo021999]

Basically, use this structure:

=Your_Formula + IF(Your_Formula>=3,-1,0)

Beside, in your formula:

COUNTIF(KI4:KI6,"TRD")+...+COUNTIF(KI4:KI6,"*D*")

in which "*D*" is including "TRD" (TRD will be counted twice)

 

[Radiatedjay2]

What version of Excel are you using? (Good idea to update your user profile to show this.) It will make a big difference in what solution to give you.

Sorry... Using Microsoft 365 for Enterprise

 

[6StringJazzer]

Here is a formula that will work in your version (but not older versions before 2021)

=LET(COUNTIF(KI4:KI6,"*N*")+COUNTIF(KI4:KI6,"PHY")+COUNTIF(KI4:KI6,"TRD")+COUNTIF(KI4:KI6,"MT")+COUNTIF(KI4:KI6,"*D*")-COUNTIF(KI4:KI6,"DC")-COUNTIF(KI4:KI6,"XD")-COUNTIF(KI4:KI6,"XN"),F,F-IF(F=>3,1,0))

 

[Radiatedjay2]

Basically, use this structure:

=Your_Formula + IF(Your_Formula>=3,-1,0)

Beside, in your formula:

COUNTIF(KI4:KI6,"TRD")+...+COUNTIF(KI4:KI6,"*D*")

in which "*D*" is including "TRD" (TRD will be counted twice)

Thanks! I did notice that about the "*D*" and the "TRD" a while back. I really haven't ever needed to use the TRD entry so it hasn't caused any issues yet. I will cut that part out in future iterations of the scheduling spreadsheet.

 

[Radiatedjay2]

Here is a formula that will work in your version (but not older versions before 2021)

=LET(COUNTIF(KI4:KI6,"*N*")+COUNTIF(KI4:KI6,"PHY")+COUNTIF(KI4:KI6,"TRD")+COUNTIF(KI4:KI6,"MT")+COUNTIF(KI4:KI6,"*D*")-COUNTIF(KI4:KI6,"DC")-COUNTIF(KI4:KI6,"XD")-COUNTIF(KI4:KI6,"XN"),F,F-IF(F=>3,1,0))

With that formula, I get an error message that says, "The first argument of LET must be a valid name"

 

[6StringJazzer]

Very sorry, I got it backwards:

=LET(F, COUNTIF(KI4:KI6,"*N*")+COUNTIF(KI4:KI6,"PHY")+COUNTIF(KI4:KI6,"TRD")+COUNTIF(KI4:KI6,"MT")+COUNTIF(KI4:KI6,"*D*")-COUNTIF(KI4:KI6,"DC")-COUNTIF(KI4:KI6,"XD")-COUNTIF(KI4:KI6,"XN"),F-IF(F=>3,1,0))

 

[Eric W]

Your original formula can be written a bit more concisely like so:

=SUM(COUNTIF(KI4:KI6,{"*N*","PHY","TRD","MT","*D*","DC","XD","XN"})*{1,1,1,1,1,-1,-1,-1})

When included in the LET to allow for the >=3 condition, it looks like:

=LET(s,SUM(COUNTIF(KI4:KI6,{"*N*","PHY","TRD","MT","*D*","DC","XD","XN"})*{1,1,1,1,1,-1,-1,-1}),IF(s>=3,s-1,s))

 

[Radiatedjay2]

Very sorry, I got it backwards:

=LET(F, COUNTIF(KI4:KI6,"*N*")+COUNTIF(KI4:KI6,"PHY")+COUNTIF(KI4:KI6,"TRD")+COUNTIF(KI4:KI6,"MT")+COUNTIF(KI4:KI6,"*D*")-COUNTIF(KI4:KI6,"DC")-COUNTIF(KI4:KI6,"XD")-COUNTIF(KI4:KI6,"XN"),F-IF(F=>3,1,0))

Thanks, that does the trick!