Get Results Based on Filtered Text Criteria
Tonight's tip concerns a formula that can be used to generate a result based on how filters are applied to text entries in a column. There are three possible results: "Always"; "Sometimes" and "Never". The result "Always" should only be returned if all selected choices are marked, "Always". Likewise, the result "Never" should only be returned if all selected choices are marked, "Never". If any one selection is "Sometimes", then the result should be "Sometimes", and if the selected entries are made up of "Always" and "Never" designations, then the result should be "Sometimes".
The choices must be listed in cells A2 to A4. This variation will include an asterisk in the result if any one of the selected entries has an asterisk. The formula is set to work for data entered in the range B12:B65000.
=IF(SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A2)),SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A2&"*")))=SUBTOTAL(3,B12:B65000),IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A2&"*"))>0,"Never*","Never"),IF(SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A4)),SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A4&"*")))=SUBTOTAL(3,B12:B65000),IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A4&"*"))>0,"Always*","Always"),IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A3&"*"))>0,"Sometimes*","Sometimes")))
This part of the formula:
SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A2))
. . . adds up the filtered entries in the range B12:B6500, for the value listed in cell A2. SUMPRODUCT multiples two arrays. The SUBTOTAL formula excludes rows hidden by the filter. OFFSET is used to generate a number for each row, so we can get a count.
The formula is repeated to search for instances of the A2 'Never' with an asterisk:
SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A2&"*")
The formula begins with IF(SUM because we add these two parts to see if they are equal to the SUBTOTAL count (3 is the SUBTOTAL code for COUNTA - which counts the number of cells)
=SUBTOTAL(3,B12:B65000)
. . . if this is true, we then search to see if there is one cell with Never and an asterisk:
IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A2&"*"))>0
. . . if this is true we get the result 'Never*'. If it's not we get 'Never'.
"Never*","Never"),
But the original IF that begins the full formula is still active. In its false result (where no filtered cells are 'Never' or 'Never*'), we enter the 'filter' formula again to see if all of the selected cells are 'Always' or 'Always*', the choice in cell A4
IF(SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A4)),SUMPRODUCT(SUBTOTAL(3,OFFSET(B12:B6500,ROW(B12:B65000)-MIN(ROW(B12:B65000)),,1))*(B12:B65000=A4&"*")))
Is there sum equal to the SUBTOTAL count of the filtered cells?
And so the process repeats itself, until we finally check to see if any cell contains 'Sometimes*". If all the preceding IF formula are FALSE, we get the final result, "Sometimes".
