Formula to return "1" or "2" (not "All") modified to allow a second column filter to be set

An accepted solution to the formula (cell AC2) to return "1", "2", or "All" using filtered col B works as intended.  

However, adding a filter to col A causes the formula to revert to "All" even though only "1" is unfiltered ("2" is hidden).  

Can a fix be applied to the formula in AC2 to return "1" when col A has a filter set to show only "1" and specific rows have been selected in col A?   File attached.count-visible-TorF.xlsx
Berry MetzgerLean process improvement consultantAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Assuming you're not concerned with blanks, try this:

=IF(AND(SUBTOTAL(104,$B$5:$B$206)=1,SUBTOTAL(105,$B$5:$B$206)=1),1,IF(AND(SUBTOTAL(104,$B$5:$B$206)=2,SUBTOTAL(105,$B$5:$B$206)=2),2,"All"))

Regards,
Rory
0
 
Berry MetzgerLean process improvement consultantAuthor Commented:
Perfect Rory!
Thank you, you've been a great help to me. And it works filtering out Blanks as well. Your formula solution completes the development of this project.  
Berry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.