Forum Discussion

kselinko's avatar
kselinko
Copper Contributor
Nov 03, 2020
Solved

COUNTIFS function help needed

Hello - I am trying to use COUNTIFS but it is not working when I add the second condition, despite being able to visually confirm there are cells that meet both conditions I've identified.  In the a...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Nov 03, 2020

    kselinko 

    All Criteria ranges of COUNTIFS must have the same size and shape. But A:F contains 6 columns while A:M contains 13 columns. Use this instead:

     

    =COUNTIFS(Data!F:F,A2,Data!M:M,1)
HansVogelaar's avatar
HansVogelaar
MVP
Nov 03, 2020

kselinko 

All Criteria ranges of COUNTIFS must have the same size and shape. But A:F contains 6 columns while A:M contains 13 columns. Use this instead:

 

=COUNTIFS(Data!F:F,A2,Data!M:M,1)
kselinko's avatar
kselinko
Copper Contributor
Nov 03, 2020

HansVogelaarThat fixed the value error but now it's just returning "0" when we can see there are 8 rows with data in columns F and M that meet the criteria.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 03, 2020

    kselinko 

    It works for me. Perhaps you have to recalculate?

     

    • kselinko's avatar
      kselinko
      Copper Contributor
      Nov 03, 2020

      HansVogelaar This feels like a stupid question, but what do you mean by 'recalculate'? What step did I miss?  I've hit F2 to refresh the cell, and SHIFT+F9 to refresh the tab but it's not updating.

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 03, 2020

        kselinko 

        That's what I meant - I don't understand why it isn't working for you.

        Here is your sample workbook with the formula. What do you see when you open it?

Resources