How do I add a third argument to countifs formula?

PaigeKucz

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

https://community.smartsheet.com/discussion/comment/362631#Comment_362631

The formula as you had it was running multiple countifs 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 says count all the rows where the Status is one of these values ("Working", "technology", etc), and where the Zone is "Zone 1", and where the submission 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

@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

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

@PaigeKucz let's simplify this a whole bunch by implementing an OR function in 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

@Jeff Reisman 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?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/362631#Comment_362631

The formula as you had it was running multiple countifs 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 says count all the rows where the Status is one of these values ("Working", "technology", etc), and where the Zone is "Zone 1", and where the submission 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

@Jeff Reisman YOU ARE THE MAN!!! THANK YOU