Forum Discussion

Dan Hanley's avatar
Dan Hanley
Copper Contributor
Oct 04, 2018

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....
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 04, 2018

Hi Dan,

 

What you try to count that's the number of cells in columns from B to F for which the value  simultaneously equals to F3 AND H3. Most probably there are no such cells. 

 

What you really would like to count it's bit hard to understand without the sample.

Dan Hanley's avatar
Dan Hanley
Copper Contributor
Oct 04, 2018

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

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Oct 05, 2018
    try:
    =COUNTIFS(B2:B11,I3,E2:E11,K3)
    HTH
    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Oct 05, 2018

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 05, 2018

        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

Resources