Cross Reference countifs formula w/multiple criteria

K@te

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"

 

 

 

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