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. I am able to successfully search the range of columns with one conditions using the formula =COUNTIF(Seniors!$B$3:$F$99,$F$3) with B3:F99 being the range of data and F3 bing the cell with the dropdown menu of conditions. Each column has two choices of data. For example Column B is gender with the choices being Male or Female. So there are ten choices in my dropdown menu to search for.  When I try to count using two conditions from two dropdown menus I am successful if I limit the columns I search with the formula =COUNTIFS(Seniors!$B$3:$B$99,$F$3,Seniors!$C$3:$C$99,$H$3) with B3:B99 and C3:C99 being the columns searched to meet the conditions from the drop down menus in cells F3 and H3 which allows me to count the number of people who meet the condition of Male or female and in a sport or not in a sport. This however limits my search ability to two columns and only two choices per dropdown menu. When I try to search all the columns for any of the ten choices in my dropdown menus using the formula =COUNTIFS(Juniors!$B$3:$F$99,$F$3,Juniors!$B$3:$F$99,$H$3) where B3:F99 are the five columns I wish to search and F3 is the first choice of ten conditions and H3 is the second choice the sheet returns a zero (0) as the answer. I do not receive an error message or any suggestions of what to change. 

Thanks you for your assistance in this matter.

excel

10 Replies

  • BobOrrell's avatar
    BobOrrell
    Iron Contributor
    Oct 05, 2018

    I might be over simplifying this, but why not put your data in a table with a total row?  You can filter by any number of the columns in any combination and return the number of rows match your criteria.  Just click the arrow next to the header, uncheck the value you do not want counted, then click ok.  To reset, with any cell in the table selected, you can go to the Data tab, and click the Clear button in the Sort & Filter section, or clear each column individually, by clicking the arrow like before, and selecting Clear Filter from "<columnname>"

  • 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

Resources