Why won't my COUNTIF formula not count anything from source sheet?

Carolina Rivera · 09/14/20

Hi all,

I currently have a system for three spreadsheets: 1) Masterlist for open/closed actions that feeds a 2) Open Actions sheet, and a 3) Closed Actions sheet. The formulas for the first two sheets work perfectly, however, when it comes to the third sheet (Closed Actions), my COUNTIFS formula will not count anything on it. I've tried several formulas to try and understand the cause of this issue, but nothing works. Below I've attached an example of my issue, along with the formula I'm using:

=COUNTIFS({date_closed}, IFERROR(YEAR(@cell), 0) = 2020, {date_closed}, IFERROR(MONTH(@cell), 0) = 1)

Thanks in advance!

Carolina

David Tutwiler · 09/14/20

I copied your formula directly into a few of my test sheets, updated the reference, and it worked just fine. My only thought is that the reference sheet might be stale or broken, or could be copied over from something else. I would start the formula over, remake the reference under a slightly different name (something like date_closed1) and see if it reacts any differently.

David Tutwiler · 09/14/20

I copied your formula directly into a few of my test sheets, updated the reference, and it worked just fine. My only thought is that the reference sheet might be stale or broken, or could be copied over from something else. I would start the formula over, remake the reference under a slightly different name (something like date_closed1) and see if it reacts any differently.

Carolina Rivera · 09/16/20

I copied your formula directly into a few of my test sheets, updated the reference, and it worked just fine. My only thought is that the reference sheet might be stale or broken, or could be copied over from something else. I would start the formula over, remake the reference under a slightly different name (something like date_closed1) and see if it reacts any differently.<\/p>","bodyRaw":"[{\"insert\":\"I copied your formula directly into a few of my test sheets, updated the reference, and it worked just fine. My only thought is that the reference sheet might be stale or broken, or could be copied over from something else. I would start the formula over, remake the reference under a slightly different name (something like date_closed1) and see if it reacts any differently.\\n\"}]","format":"rich","dateInserted":"2020-09-14T12:49:36+00:00","insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-08-24T21:03:19+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/262710#Comment_262710","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/262710#Comment_262710

Thanks David!

我想最好还是复制数据a new sheet and update the formulas, which somehow solved the issue.

David Tutwiler · 09/17/20

Interesting. Well, glad you got it working.

Marc Roussel · 06/29/21

Greetings,

I came across a similar situation with metrics production. In some cases COUNTIFS give me a value and if I use COUNT(COLLECT( and apply teh same criteria, I get another value, usually +1 from COUNTIFS. When I check the source data, teh COUNT(COLLECT( produces the right tally but COUNTIFS doesn't. I can't figure it out because it doesn't always happen. I can't find rhyme or reason. I decided to use COUNT(COLLECT(... going forward.

Best regards,

Marc Roussel

Why won't my COUNTIF formula not count anything from source sheet?

Best Answer

Answers

Help Article Resources

Categories