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 (*******)")

 

[dreid1011]

Maybe this to get started:

=COUNTIFS(D4:D23,"019 - Call Center (*******)",H4:H23,"<>Released",H4:H23,"<>Resigned",H4:H23,"<>Not in Class")

 

[fmorenojr]

Maybe this to get started:

=COUNTIFS(D4:D23,"019 - Call Center (*******)",H4:H23,"<>Released",H4:H23,"<>Resigned",H4:H23,"<>Not in Class")

This worked. Thank you.

 

[dreid1011]

This worked. Thank you.

You're welcome, happy to help.

 

[fmorenojr]

@dreid1011

I just forgot, what if I'm a second sheet?

the main sheet "Roll Call (CC-BOS)" is where all the formulas are used. so, when I'm using the second sheet to count its "=COUNTIF('Roll Call (STOREFRONT-VEC)'!D4:D23,"03 - Storefront (****)")"

i tried to mimic the same formula that you provided above, but the second portion I'm not sure how to add it so it knows to use the second sheet "Roll Call (STOREFRONT-VEC)" when it comes to the release, resigned, not in class.

 

[dreid1011]

@dreid1011

I just forgot, what if I'm a second sheet?

the main sheet "Roll Call (CC-BOS)" is where all the formulas are used. so, when I'm using the second sheet to count its "=COUNTIF('Roll Call (STOREFRONT-VEC)'!D4:D23,"03 - Storefront (****)")"

i tried to mimic the same formula that you provided above, but the second portion I'm not sure how to add it so it knows to use the second sheet "Roll Call (STOREFRONT-VEC)" when it comes to the release, resigned, not in class.

When does the formula need to change to use the second sheet?

 

[fmorenojr]

When does the formula need to change to use the second sheet?

it's basically the same as the original, it's just that the second sheet has the other locations like storefront and VEC.

Sheet 1 is: Roll Call (CC-BOS)
Sheet 2 is: Roll Call (STOREFRONT-VEC)

this is what you provided (which is being used for sheet 1)
=COUNTIFS(D4:D23,"019 - Call Center (******",H4:H23,"<>Released",H4:H23,"<>Resigned",H4:H23,"<>Not in Class")

I believe this part works, just cannot make the second part work.
=COUNTIF('Roll Call (STOREFRONT-VEC)'!D4:D23,"02 - Storefront (****)") just now sure how to get the "released, resigned, and not in class to work where its being pulled from the second sheet (Roll Call (STOREFRONT-VEC))

 

[dreid1011]

it's basically the same as the original, it's just that the second sheet has the other locations like storefront and VEC.

Sheet 1 is: Roll Call (CC-BOS)
Sheet 2 is: Roll Call (STOREFRONT-VEC)

this is what you provided (which is being used for sheet 1)
=COUNTIFS(D4:D23,"019 - Call Center (******",H4:H23,"<>Released",H4:H23,"<>Resigned",H4:H23,"<>Not in Class")

I believe this part works, just cannot make the second part work.
=COUNTIF('Roll Call (STOREFRONT-VEC)'!D4:D23,"02 - Storefront (****)") just now sure how to get the "released, resigned, and not in class to work where its being pulled from the second sheet (Roll Call (STOREFRONT-VEC))

Okay, let me see if I understand. The formulas are only on the first sheet, and some of them need to target other sheets? When they target the other sheets, is all the data required for the formula on the target sheet or is some of it also on the first sheet?

 

[fmorenojr]

Okay, let me see if I understand. The formulas are only on the first sheet, and some of them need to target other sheets? When they target the other sheets, is all the data required for the formula on the target sheet or is some of it also on the first sheet?

That is correct, all the formulas are on the first sheet, there is data on the second sheet that will be needed. so basically, the second sheet has the data that is needed, just needs to be visible on the first sheet. Hopefully this makes sense.

 

[dreid1011]

That is correct, all the formulas are on the first sheet, there is data on the second sheet that will be needed. so basically, the second sheet has the data that is needed, just needs to be visible on the first sheet. Hopefully this makes sense.

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")