COUNTIFS from multi-select drop down
Hi Community, hope you are well!
I would really appreciate some help with the following formula.
In a master metric sheet I would like to count multiple criteria. I am using this formula which works great since it is automated once the @row are filled in by my Team:
=COUNTIFS({Planning sheet Range 2}, PA@row, {Planning sheet Range 3}, Year@row, {Planning sheet Range 4}, Status@row)
The issue is when the "Status@row" is a multi-select, the formula above would count with an implicit AND function, thus, when all the criteria in the multiselect are met. In my case I am interested in an OR function, hence, given the PA and the Year I want to count multiple status from the multiselect at once.
I can opt for the following:
=COUNTIFS({Planning sheet Range 2}, PA@row, {Planning sheet Range 3}, Year@row, {Planning sheet Range 4}, OR(@cell = "Scheduled", @cell = "Posted to System"))
The problem is that this formula would require my Team to edit the Status section of the formula each time - adding and removing elements of the OR function - rather than simply working on the drop down column.
Hope my question is clear enough and very grateful to whom will spend time to help me!
Best,
Alessandro
Best Answer
-
In regards to your question about blank PA , YEAR , or STATUS.
You can add an OR statement into your formula. The syntax would be as shown below:
=COUNTIFS({Planning sheet Range 2},Β OR(ISBLANK(PA@row),@cell = PA@row) , {Planning sheet Range 3},Β OR(ISBLANK(Year@row),@cell = Year@row), {Planning sheet Range 4},Β OR(ISBLANK(Status@row), HAS(Status@row, @cell)))
Answers
-
Why does your team need to edit the "@row" portion of the formula? What exactly are they changing?
As for the status options... How many different options could there be selected at any one time?
-
Hi Paul,
my Team won't ever edit the @row indeed, in the case of the second formula
=COUNTIFS({Planning sheet Range 2},Β PA@row, {Planning sheet Range 3},Β Year@row, {Planning sheet Range 4}, OR(@cell = "Scheduled", @cell = "Posted to System")
they would need to change the @cell only. My question is how to avoid this to happen? I would like them to be able to fill up information columns (PA [text], Year [text], Status [multi drop down]) only without editing any formula. Hence, a formula that would behave like the first formula:
=COUNTIFS({Planning sheet Range 2},Β PA@row, {Planning sheet Range 3},Β Year@row, {Planning sheet Range 4},Β Status@row)
BUT allowing the Status@row to be multi-select and counting when more than one status are combined even if they are not combined in the source sheet.
Re: How many different options could there be selected at any one time? Between 2 and 4 maximum
-
You could create some extra single select columns and reference them in the formula. Have your team make a single selection in each of the 4 columns (or leave blank as applicable).
-
Yes, I thought about the extra columns but wanted to make sure an easier solution wasn't available. I understand it's not.
Thank you Paul.
One more question, please if you don't mind. In the first formula:
=COUNTIFS({Planning sheet Range 2},Β PA@row, {Planning sheet Range 3},Β Year@row, {Planning sheet Range 4},Β Status@row)
When in the METRICS sheet one of the values the @row components reference to (either the PA, Year or Status) is blank, the formula would return 0 because it is counting how many times in the SOURCE sheet the exact combination (which includes the blank cell) can be found. Let's assume that in the SOURCE sheet PA/Year/Status are always assigned, thus never blank.
Would I want the formula to return in the METRICS sheet though is to automatically count also when one of the elements is missing. This means that in the METRICS my Team can also have combinations as the following:
PA + Year + No Status: count how many times PA (1) in Year (2020) regardless of the status
No PA + Year + Status: count regardless of the PA, Status (In Progress) occur in Year (2020)
PA + No Year + Status: count how many times PA(1) is in Status (In Progress) regardless of the Year
Further example: PA(1) + No Year + No Status: returns how many times PA(1) is observed in the source sheet regardless of the year and status.
Would this be possible?
-
Regarding your original multi select issue the below should solve that by using the has function.
This would allow you to choose multiple statuses in the status cell and any row that matches one of those statuses would be counted.
=COUNTIFS({Planning sheet Range 2},Β PA@row, {Planning sheet Range 3},Β Year@row, {Planning sheet Range 4},Β HAS(Status@row, @cell))
-
In regards to your question about blank PA , YEAR , or STATUS.
You can add an OR statement into your formula. The syntax would be as shown below:
=COUNTIFS({Planning sheet Range 2},Β OR(ISBLANK(PA@row),@cell = PA@row) , {Planning sheet Range 3},Β OR(ISBLANK(Year@row),@cell = Year@row), {Planning sheet Range 4},Β OR(ISBLANK(Status@row), HAS(Status@row, @cell)))
-
@Leibel S It looks like you may be on to something, but you are going to want to change your ISBALNK statements to contain an @cell reference instead of [Column Name]@row].
-
@Paul Newcome The criteria needs to return true as long as either the PA@row is blank (then it always returns true regardless of the value in the range), OR is the @cell from the Range matches the PA@row.
-
@Leibel S Excellent, it seem to work perfectly!! I will test further but looks great.
I am very grateful for your brilliant solution.(:
Thank you @Leibel S and @Paul Newcome for your time spent to help me out!
-
My pleasure.
After figuring this one out I went back to an older formula and was able to make it half the size using this technique :)
Help Article Resources
Categories
Check out the Formula Handbook template!