Multiple Criteria

AliT

I am working on pulling information using a countifs

=COUNTIFS({020-Contracts - In Queue Range 1}, "Agency Wide", {020-Contracts - In Queue Range 3}, "Requested")

Here in lies my issue, for the range 3, I want it to only count if its any of these:

1-Requested
2-Assigned
3-In Progress
4-Approvals Requested
5-Obtaining Signatures

Is there a way to include them all? (I have others like On hold or Cancelled that I do not want counted.

Any help will be greatly appreciated.

Ali

Paul Newcome

We could either count all of them or exclude everything else. Which list is shorter? The ones you want to include or the ones you want to ignore?

Include:

=COUNTIFS({Range}, OR(@cell = "This", @cell = "That", @cell = "Something Else"))

Exclude:

=COUNTIFS({Range}, AND(@cell <> "This", @cell <> "That", @cell <> "Something Else"))

AliT

I think I have another issues that is barring that from working.

This is the data that I am trying to modify to not pull the # of contracts or the amount for 4 different status' (shorter list)

And the formula being used in #of Contracts column is

=COUNTIF({020-Contracts - In Queue Range 1}, "ABS")

and I wanted to modify by using Countifs. But I can't point it to a @row because its not there. I didn't build this sheet and I don't want to start from scratch.

Paul Newcome

So you are wanting to modify your existing COUNTIF to a COUNTIFS and "filter" the results by excluding certain statuses? In that case, you would just need to add the S on there and then add another range/criteria set. The range would be the status column in the source sheet and the criteria would be the AND as demonstrated in my previous post.

Although I'm not sure I follow what you mean when you reference "@row".

AliT

Hi Paul,

I mistyped and meant @cell. I will see if I can get this to work.

Thank you for your help.

Paul Newcome

@cell just tells the formula to evaluate the previously established {Range} on a cell by cell basis. The @ceelll reference doesn't really care what sheet it is on.

For example, your existing formula

=COUNTIF({020-Contracts - In Queue Range 1}, "ABS")

could also be written as

=COUNTIF({020-Contracts - In Queue Range 1}, @cell = "ABS")

without making any difference to the count at all.

But you do need it when using the AND or OR function like we are.