Cross Reference countifs formula w/multiple criteria

I am trying to count if a dept name is matched then count 2 different criteria's in the same column (all in another sheet)

I can get it to count all of the approved but I can not get it to count both

This is what is working =COUNTIFS({Tracking Range 2}, "Dept", {Tracking Range 1}, "Approved")

How do I add the other criteria? "Pre-Approved"

Find more posts tagged with

Comments

Nic Larsen

Try:

=COUNTIFS({Tracking Range 2}, "Dept", {Tracking Range 1}, "Approved") + COUNTIFS({Tracking Range 2}, "Dept", {Tracking Range 1}, "Pre-Approved")

This one will also work:

=COUNTIFS({Tracking Range 2}, "Dept", {Tracking Range 1}, OR(@cell = "Approved", @cell = "Pre-Approved"))

K@te

Thank you.

I tried the first one before, it did not work. I probably missed something.

Your help is appreciated.

Nic Larsen

Hmm.. It may be that it won't work if just copy and paste since there are cross sheet references but the structure should work. Or maybe I missed something. The 2nd one worked for me when I set it up in a test scenario in my own sheets.

K@te

One more question.

If I wanted it to include another column for my results how would I write that?

I tried the following.

=COUNTIFS({Tracking Range 2} "Dept",And( {Track Range3},"Q1"), {Tracking Range 1}, OR(@cell = "Approved", @cell = "Pre-Approved"))

Show

K@te

Thank you, the second one worked for me too.

if I add add another scenario to the equation it isn't working for me.

i am cross referencing another sheet.

find Dept(column1) and Q1(column2) then count all of the approved and preapproved

its not liking any way I write this.

Paul Newcome

=COUNTIFS({Tracking Range 2}, @cell = "Dept", @{Track Range3}, @cell ="Q1", {Tracking Range 1}, OR(@cell = "Approved", @cell = "Pre-Approved"))

Just a couple of minor tweaks... You were missing a comma between your first range and criteria, and the AND function for the additional range was unnecessary. I also added the @cell reference not because it is absolutely necessary, but because it's one of my little OCD quirks. Haha.

K@te

Thank you for your help. I really appreciate you letting me know where my issue was.

Sometimes looking at these too long will make you go blind

Paul Newcome

No worries. Happy to help.

I take regular "eye health breaks" (aka staring off into space) most days. Haha

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.