How to subtract when a specific text is used.

[fmorenojr]

Hello,

I am trying to figure out how to subtract if the status was changed to 'Release", "Resigned", and "Not in Class". looking at the picture below, when a "LOC" is assigned, its calculated to the right. (Blue Square: =SUM(J21:J34)) (Green Square: =SUM(J14:J17)). Using the pic below there are 3 agents, the totals show 3, if the status shows "Released", "Resigned", and "Not in Class" then it would subtract. With those it should subtract 2 and now leaves only 1. Hopefully this makes sense.

The formula for each location is done as follows: =COUNTIF('Roll Call (CC-BOS)'!D4:D23,"019 - Call Center (*******)")

 

[fmorenojr]

Is all the data on the second sheet? Otherwise, just add in the sheet names before the ranges like this:

=COUNTIFS('Roll Call (STOREFRONT-VEC)'!D4:D23,"02 - Storefront (****)",'Roll Call (STOREFRONT-VEC)'!H4:H23,"<>Released",'Roll Call (STOREFRONT-VEC)'!H4:H23,"<>Resigned",'Roll Call (STOREFRONT-VEC)'!H4:H23,"<>Not in Class")

this worked. Thank you! 4 thumbs up for you!!