Cross Reference countifs formula w/multiple criteria

K@te · 02/14/19

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 · 02/14/19

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 · 02/15/19

Thank you.

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

Your help is appreciated.

Nic Larsen · 02/15/19

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 · 02/15/19

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 · 02/16/19

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 · 02/18/19

=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 · 02/18/19

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 · 02/18/19

No worries. Happy to help.

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

Cross Reference countifs formula w/multiple criteria

Comments

Categories