Count if (Advanced) Contact Field

JP Pedicino

I am having trouble counting the number of contact or lack of in two different instances.

Count Assigned to Business

Count non-blank contacts (exclude those that contain the word "business")

First Metric:

I would like to count the number of times my contact field has the word "Business" in it. I've tried the formula below and I get #UNPARSEABLE

=COUNTIF({Import RFI's - NO TECHNICAL DATA Range 1}, FIND("Business"@cell))

Second Metric:

I would like to count the number of time that {Import RFI's - NO TECHNICAL DATA Range 1} is ISNOTBLANK, while excluding any instances that have the word "Business" them.

Bassam Khalil

Hi @JP Pedicino

Hope you are fine, for the first formula please try the following:

=COUNTIF({Import RFI's - NO TECHNICAL DATA Range 1}, CONTAINS("Business", @cell))

and for second Formula try the following:

=COUNTIFS({Import RFI's - NO TECHNICAL DATA Range 1}, AND(NOT(CONTAINS("Business", @cell)),
@cell <> ""))

JP Pedicino

Thank you for your patients. I don't know what the issue is here.

When I use the formula below I get a total number of 41, however when I filter the data the total is 17.

=COUNTIFS({Import RFI's - NO TECHNICAL DATA Range 1}, AND(NOT(CONTAINS("Business", @cell)),
@cell <> ""))

Bassam Khalil

Hi @JP Pedicino

Hope you are fine, for the first formula please try the following:

=COUNTIF({Import RFI's - NO TECHNICAL DATA Range 1}, CONTAINS("Business", @cell))

and for second Formula try the following:

=COUNTIFS({Import RFI's - NO TECHNICAL DATA Range 1}, AND(NOT(CONTAINS("Business", @cell)),
@cell <> ""))

JP Pedicino

I still need a little refinement. If you can assist that would be great!

=COUNTIFS({Import RFI's - NO TECHNICAL DATA Range 1}, AND(NOT(FIND("Business", @cell))))

The problem here was my fault. I did not explain myself properly. I need to have the blanks removed from the count as well.

This one worked perfect, thank you!

=COUNTIF({Import RFI's - NO TECHNICAL DATA Range 1}, CONTAINS("Business", @cell))

Bassam Khalil

@JP Pedicino

The second formula in my replay will calculate the not blank an not contain busses. Please check it.

=COUNTIFS({Import RFI's - NO TECHNICAL DATA Range 1}, AND(NOT(CONTAINS("Business", @cell)),
@cell <> ""))

JP Pedicino

Thank you for your patients. I don't know what the issue is here.

When I use the formula below I get a total number of 41, however when I filter the data the total is 17.

=COUNTIFS({Import RFI's - NO TECHNICAL DATA Range 1}, AND(NOT(CONTAINS("Business", @cell)),
@cell <> ""))

JP Pedicino

Got it, there was two issue here. I called support for assistance and they said that the formula was beautifully written (applause to you), there was an issue with their back-end. The other was that the formula since referencing the contact list has to use FIND instead of contains due to the field type not being compatible with CONTAINS.

Thank you again for your support!

I owe you one!

Bassam Khalil

@JP Pedicino

Excellent, am happy that you find the problem, and I will be happy to help you any time.

Please help the Community by accepting my answer and I will appreciate your Vote Up.