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....
Here is an example of what I am trying to do. I created a smaller version to share. The data is in columns B:F, I3 and K3 are the dropdown menus, and the formula =COUNTIFS(B2:F11,I3,B2:F11,K3) is in I6. As you can see there are two rows that match the conditions of Female and Choir but the formula returns 0
try:
=COUNTIFS(B2:B11,I3,E2:E11,K3)
HTH
=COUNTIFS(B2:B11,I3,E2:E11,K3)
HTH
arrange your lookup - do not mix up all data in one list
also you can have more than 2 criteria - just lookup at the proper column
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
Mr. Baklan
thank you for the code.
It is more "Dynamic" ..
though quite long but very neat!
pls see attached file .
I have arranged the lookup style.
hope you can work something out of it.
thanks..
