COUNTIFS with multiple criteria

Martin Hughes

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.

Thanks in advance.

Bassam Khalil

@Martin Hughes

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

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

Martin Hughes

Sorry Bassam, please see above.

Bassam Khalil

@Martin Hughes

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

Martin Hughes

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

Bassam Khalil

@Martin Hughes

Please Try the following formula:

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

Martin Hughes

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

Bassam Khalil

@Martin Hughes 

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 Khalil

@Martin Hughes

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

Martin Hughes

@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

Bassam Khalil

@Martin Hughes

Excellent, and I will be happy to help you any time.