COUNTIF functions returning 0

roxxtheboxx · 04/16/24

I am trying to create a countif formula that references one other sheet. I want it to pull with 2 different criteria if the column = 01 and the second column = "open".

=COUNTIFS({Dump: OPEN AND CLOSED REQS Range 1}, "01", {Dump: OPEN AND CLOSED REQS Range 2}, "Open"). This function returns 0 when I know there is a few that have 01 . I also tried inserting 01, 02, 03 ,04 and I still get 0 rather than the correct number.

Please let me know if you need more information.

smartsheet datadump.png

smartsheet metrics sheet 2.png

Courtney S. · 04/17/24

Someone else on a different post I recall got a COUNTIFS function to work better by adding "@cell=" in the criterion portion even though the official Smartsheet function page doesn't mention that. Give it a try, let me know if that does work?

=COUNTIFS({Dump: OPEN AND CLOSED REQS Range 1}, @cell=1, {Dump: OPEN AND CLOSED REQS Range 2}, @cell="Open")

Nick Korna · 04/16/24

Hi @roxxtheboxx,

Amend your formula slightly:

=COUNTIFS({Dump: OPEN AND CLOSED REQS Range 1}, VALUE(@cell)=1, {Dump: OPEN AND CLOSED REQS Range 2}, "Open")

This should fix the issue for you, but if you've any problems/questions just let us know!

roxxtheboxx · 04/16/24

Hi Nick ! It returns as 1 when it should be counting 5. Is there something else I can try.

Courtney S. · 04/17/24

Someone else on a different post I recall got a COUNTIFS function to work better by adding "@cell=" in the criterion portion even though the official Smartsheet function page doesn't mention that. Give it a try, let me know if that does work?

=COUNTIFS({Dump: OPEN AND CLOSED REQS Range 1}, @cell=1, {Dump: OPEN AND CLOSED REQS Range 2}, @cell="Open")

Nick Korna · 04/18/24

https://community.smartsheet.com/discussion/comment/431366#Comment_431366

Can you paste a screenshot of the filtered data (hide/obscure any unnecessary columns)?

COUNTIF functions returning 0

Best Answer

Answers

Help Article Resources

Categories