How do I add a third argument to countifs formula?

PaigeKucz · 02/15/23

I am trying to only show dashboard data from the sheet IF the submitted date is from the current year - we do have a helper column for the submitted year but I am unsure how to incorporate it within my formula below.

I am trying to count if the status is Working, Next up- top opportunities, Pilot/implemented, Monitor/check, and Technology, Submitted by Zone 1, and the submission date is current year (so in this case 2023)

Any guidance would be very helpful ! Thank you

=COUNTIFS({APD CI Tracker Range 5}, "Working", {APD CI Tracker Range 8}, "Zone 1") + COUNTIFS({APD CI Tracker Range 5}, "Next up - Top Opportunities", {APD CI Tracker Range 8}, "Zone 1") + COUNTIFS({APD CI Tracker Range 5}, "Pilot/ Implemented", {APD CI Tracker Range 8}, "Zone 1") + COUNTIFS({APD CI Tracker Range 5}, "Monitor/ Check", {APD CI Tracker Range 8}, "Zone 1") + COUNTIFS({APD CI Tracker Range 5}, "Technology", {APD CI Tracker Range 8}, "Zone 1")

Jeff Reisman · 02/16/23

@Jeff Reisman<\/a> the purpose of the dashboard is to show a count of any opportunity with that status, Zone 1, and submitted this year - will the OR formula cause it to only count one status or the other?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jeff Reisman\",\"userID\":86866}}},{\"insert\":\" the purpose of the dashboard is to show a count of any opportunity with that status, Zone 1, and submitted this year - will the OR formula cause it to only count one status or the other?\\n\"}]","format":"rich","dateInserted":"2023-02-16T14:27:54+00:00","insertUser":{"userID":158350,"name":"PaigeKucz","url":"https:\/\/community.smartsheet.com\/profile\/PaigeKucz","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-03T18:11:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/362631#Comment_362631","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/362631#Comment_362631

你的公式是运行多个故事ntifs and adding the results. So you wanted all rows with a status of "Working" and a Zone of "Zone 1", plus all rows with a status of "Next up - Top Opportunities" and a Zone of "Zone 1", etc.

Using OR does the same thing, except it does it all in a single COUNTIFS function. It sayscount all the rowswhere theStatus is one of these values("Working", "technology", etc), and where theZone is "Zone 1", and where thesubmission year is equal to the current year.

Here it is at work in a test sheet. See that it counts the rows where the value is one of those listed in the OR, and doesn't count the others:

Mike TV · 02/15/23

@PaigeKucz

https://help.smartsheet.com/function/countifs

I would provide you with a working formula possibly but what you've got above is confusing to me without seeing your sheet and what the ranges you've mapped are set to. Hopefully the help sheet will set you on the path.

PaigeKucz · 02/16/23

Does this help ?@Mike TV

There are several status's, zones, and submission years generated from the intake form

for the dashboard I am only trying to show those status's, "Zone 1", and current year submission

Jeff Reisman · 02/16/23

@PaigeKuczlet's simplify this a whole bunch by implementing anOR functionin there, allowing us to use just a single COUNTIFS, plus add the year criteria.

=COUNTIFS({APD CI Tracker Range 5},OR(@cell = "Working", @cell = "Next up - Top Opportunities", @cell = "Pilot/ Implemented", @cell = "Monitor/ Check", @cell = "Technology"), {APD CI Tracker Range 8}, "Zone 1",{Create Ref to APD CI Tracker Submission year column}, @cell = YEAR(TODAY()))

In English, count all the rows where the value in range 5 is one of these values ("Working", "technology", etc), and where the value in Range 8 is "Zone 1", and where the value in submission year is equal to the current year.

PaigeKucz · 02/16/23

@Jeff Reismanthe purpose of the dashboard is to show a count of any opportunity with that status, Zone 1, and submitted this year - will the OR formula cause it to only count one status or the other?

Jeff Reisman · 02/16/23

@Jeff Reisman<\/a> the purpose of the dashboard is to show a count of any opportunity with that status, Zone 1, and submitted this year - will the OR formula cause it to only count one status or the other?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jeff Reisman\",\"userID\":86866}}},{\"insert\":\" the purpose of the dashboard is to show a count of any opportunity with that status, Zone 1, and submitted this year - will the OR formula cause it to only count one status or the other?\\n\"}]","format":"rich","dateInserted":"2023-02-16T14:27:54+00:00","insertUser":{"userID":158350,"name":"PaigeKucz","url":"https:\/\/community.smartsheet.com\/profile\/PaigeKucz","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-03T18:11:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/362631#Comment_362631","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/362631#Comment_362631

你的公式是运行多个故事ntifs and adding the results. So you wanted all rows with a status of "Working" and a Zone of "Zone 1", plus all rows with a status of "Next up - Top Opportunities" and a Zone of "Zone 1", etc.

Using OR does the same thing, except it does it all in a single COUNTIFS function. It sayscount all the rowswhere theStatus is one of these values("Working", "technology", etc), and where theZone is "Zone 1", and where thesubmission year is equal to the current year.

Here it is at work in a test sheet. See that it counts the rows where the value is one of those listed in the OR, and doesn't count the others:

PaigeKucz · 02/16/23

@Jeff ReismanYOU ARE THE MAN!!! THANK YOU

How do I add a third argument to countifs formula?

Best Answer

Answers

Help Article Resources

Categories