COUNTIFS with multiple criteria

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

All comments

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

Sorry Bassam, please see above.

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

@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]))

Please Try the following formula:

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

@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.

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