Forum Discussion
Using countifs with two variables searching a range of columns
I am trying to create an interactive worksheet that counts the number of times two conditions are met using a drop down menu to select the conditions. The conditions are hosted in a range of columns....
However, if lookup in one list that could be
=SUMPRODUCT( (($B$2:$B$11=$I$3)+($C$2:$C$11=$I$3)+($D$2:$D$11=$I$3)+($E$2:$E$11=$I$3)+($F$2:$F$11=$I$3))* (($B$2:$B$11=$K$3)+($C$2:$C$11=$K$3)+($D$2:$D$11=$K$3)+($E$2:$E$11=$K$3)+($F$2:$F$11=$K$3)))
and attached
Thanks for all the answers. It appears to me that all the suggestions limit my search to two columns and less conditions than I want. I stumbled on a work around by adding a countif in column G that counts the number of cells that match the conditions of the two dropdown menus using the formula =(COUNTIF(B2:F2,$J$3)*1)+(COUNTIF(B2:F2,$L$3)*1). The number 0, 1, or 2 is displayed in column G. I then count the number of times 2 appears in column G and that is the number of people who match the conditions I am looking for.
Dan, suggestions do not limit you to two columns only, especially Bob's one.
Anyway, if with helper column you may clean your formula to
=COUNTIFS(B2:F2,$I$3)+COUNTIFS(B2:F2,$K$3)