How to use COUNTIFS where there are 3 conditions (columns to be referenced)
I'm trying to create a formula in a metric sheet that is referencing another sheet where data is held. I've been able to set up a COUNTIFS formula that references 2 columns so far, but my issue is that it doesn't seem to let me reference a third column using the "formula builder" as below:
I'm trying to use COUNTIFS to count all instances where, for example:
the RAG status is Red symbol (the column this would reference in the other sheet would be Action #1 Status)
the action type is News Content (the column this would reference in the other sheet would be Type of Action #1)
the team is News Programmes & Commissioning (the column this would reference in the other sheet would be Your Area)
I'm thinking my formula may need to be changed to include either an "IF", "HAS", "CONTAINS" or "AND" statement, but I'm struggling to figure it out.
Is what I'm trying to do possible? If so, how do I go about it?
Best Answer
-
Scrap that! I think I've managed it.. I copied and pasted the formula I'd already done and then was able to click just after COUNTIFS( and the "formula builder" appeared again to let me reference the other sheet phewww!
=COUNTIFS({Action Status Range 1}, "Red", {Action Type 1}, [Action Type]@row, {Area Range 1}, "News Content")
Answers
-
Good morning,
Sometimes the helper view just goes away and you have to just keep on filling out the formula without it. If you just add another , after the second criteria and do the same for the 3rd it will work..
=COUNTIFS([Apt date 2]2:[Apt date 2]17, "Scheduled", [Helper column (within the past 7 days)]2:[Helper column (within the past 7 days)]17, 1, [R1]2:[R1]17, "Yes")
-
You can use as many range/criteria sets as you need as long as your formula does not exceed 4,000 characters (with spaces). Of course showing that in the formula builder would make it absolutely obnoxious, so they only show that little piece because everything after that is just repeating the same syntax of "range comma criteria comma range comma criteria................".
-
Thanks for your speedy replies, Cory & Paul!
What I'm struggling to do though is be able to use the "Reference Another Sheet" option for the 3rd parameter (i.e. 3rd column in the other sheet), how do I do that when the option to do so within the formula builder disappears?
-
Scrap that! I think I've managed it.. I copied and pasted the formula I'd already done and then was able to click just after COUNTIFS( and the "formula builder" appeared again to let me reference the other sheet phewww!
=COUNTIFS({Action Status Range 1}, "Red", {Action Type 1}, [Action Type]@row, {Area Range 1}, "News Content")
-
@SteCoxy yea that can be a pain, I usually try deleting a , then putting it back usually re-triggers that reference link to show. On the formula I sent above it didn't show the Ref link for the 3rd until I added a comma then deleted it once I did that the reference link came back alowing me to jump to a different sheet. Glad you figured it out.
-
I wouldn't have got there without you saying it was possible, so thanks a bunch for nudging me in the right direction :-)
Help Article Resources
Categories
Check out the Formula Handbook template!
