COUNTIF functions returning 0

roxxtheboxx

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.

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

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

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

Courtney S.

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

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

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