# COUNTIFS with multiple criteria

Hi all,

I am looking to count a data range based on the criteria in two columns (yes, I'm British, hence the spelling!)

The fist column is a simple choice of three individual words, Ordered/Live/Complete. However, the second column has all our different contract types which I've narrowed down for the purposes of this question.

So, basically, I want a count of all the data that in column [STATUS] reads "Live" and that in column [CONTRACT TYPE] includes either "Cont" or "Affinity". I suppose at this point I should say that the [CONTRACT TYPRE] cells contain a lot more than the text I've offered up here but this is the best I can do in my attempts to make the statement as short as possible.

I would paste in what I've tried to construct but I think I'm confusing myself.

• ✭✭✭✭✭✭

I did the formula on the shared sheet to count using the 2 criteria "Cont" & "Affinity", Please check it.

`=COUNTIFS([CONTRACT TYPE]:[CONTRACT TYPE], OR(CONTAINS("Cont ", @cell), CONTAINS("Affinity", @cell)))`

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
edited 07/26/21

Hi @Martin Hughes

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

For the criteria you mentioned in your question you can use the following formula:

`=IFERROR(COUNTIFS(STATUS:STATUS, @cell = "Live", [CONTRACT TYPE]:[CONTRACT TYPE], OR(@cell = "Cont", @cell = "Affinity")), "")`

the following screenshot shows the result

bassam.khalil2009@gmail.com

• Sorry Bassam, please see above.

• ✭✭✭✭✭✭

Did you want the formula to count the (Status = Complete) and (CONTRACT TYPE contains "Contract hire") ?

bassam.khalil2009@gmail.com

• @Bassam Khalil Almost.

The CONTRACT TYPE column contains many more types, one where an entry is truncated to "Cont Hire xxxxx" xxxxx being variable text. Hence why just looking for "Cont" seemed to be the easiest way, along with another contract type referenced as "Affinity xxxxx"

I'm trying to construct

=COUNTIFS([CONTRACT STATUS]:[CONTRACT STATUS] = "Live", CONTAINS("Cont", [CONTRACT TYPE]:[CONTRACT TYPE]), CONTAINS("Affinity", [CONTRACT TYPE]:[CONTRACT TYPE]))

• ✭✭✭✭✭✭

`=IFERROR(COUNTIFS([CONTRACT STATUS]:[CONTRACT STATUS], @cell = "Live", [CONTRACT TYPE]:[CONTRACT TYPE], OR(@cell = "Cont", @cell = "Affinity")), "")`

bassam.khalil2009@gmail.com

• @Bassam Khalil Your answer gives me a count of zero as I think your build column is including specific text.

I would try and build a copy of the sheet for you but there's too many conversations to delete.

• ✭✭✭✭✭✭

If you can share me as an admin on a copy of your sheet (after removing or replacing any sensitive information) i will create the exact formula for you.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭

I did the formula on the shared sheet to count using the 2 criteria "Cont" & "Affinity", Please check it.

`=COUNTIFS([CONTRACT TYPE]:[CONTRACT TYPE], OR(CONTAINS("Cont ", @cell), CONTAINS("Affinity", @cell)))`

bassam.khalil2009@gmail.com

• @Bassam Khalil Thank you so much for this. The formula didn't count the word 'Contract' which I though it would by using just 'Cont' but now, seeing the formula, it's easy enough to add that in too.

Thanks again for your time. It is appreciated.

Martin

• ✭✭✭✭✭✭