CountIF using AND

D. Scott Smith

Hi all,

Can anyone tell me what might be wrong with this function below?

=COUNTIF((AND([Field CSA]:[Field CSA],"Name"),([Status]:[Status],"TEMP")))

I need to count the occurrences where the Field CSA column has the word Name and where the status column has the word TEMP. 

When each value appears in their respective columns on a given row, I need to count the occurrence of that on the sheet.

Thank you in advance for your help and assistance.

Scott

Shaine Greenwood

Try a COUNTIFS function instead: https://help.smartsheet.com/articles/775363-using-formulas#countifs

D. Scott Smith

Thanks Shaine...

Like this? 

=COUNTIFS(Field CSA:Field CSA, "Name", [Status]:[Status], "TEMP") 

This formula returns #Unparsable.  :-(

Any thoughts?

Scott

Shaine Greenwood

You need brackets around your "Field CSA" column (or any column name with spaces) when you reference it in a formula:

=COUNTIFS([Field CSA]:[Field CSA], "Name", Status:Status, "TEMP")

This counts any items that meet both criteria.

D. Scott Smith

AH!  

Thank you Shaine!  Very much appreciated!

Scott

judy.tsao54251

I followed the similar thought for my smartsheet but my still comes back with #unparsable.  What should I do? 

=COUNTIFS($Status$1:$Status$69,"Completed";$[Duplicate Project]$1:$[Duplicate Project]$69,"D")

Sidney

I'm having the same issue referencing other sheet values. I am referencing two columns from another sheet. Any assistance would be extremely appreciated. 

 

=COUNTIFS({Instant Incentive Distributor Tracking - A Range 2}, "PY8", {Instant Incentive Distributor Tracking - A Range 3}, "Yes")