Nov 03 2020 12:58 PM - edited Nov 03 2020 01:03 PM
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 attached example, I need to count the number of rows in spreadsheet "Data" for which the contents of column D match A2 in spreadsheet "Reference". This function works fine with COUNTIF and results in 8 as shown in K2. When I add a condition that asks Excel to then count the number of rows with "1" in column K, I get a #VALUE! error. The formula below is used in L2 in the Reference tab of the attached Example report. What am I doing wrong? How do I get Excel to count these for me? I have a report with thousands of rows of these data that I need to aggregate and don't have time to use a pivot table for every item.
=COUNTIFS(Data!A:D,A2,Data!A:K,"1")
Nov 03 2020 01:05 PM
SolutionAll 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)
Nov 03 2020 01:08 PM
@Hans VogelaarThat 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.
Nov 03 2020 01:12 PM
Nov 03 2020 01:27 PM - edited Nov 03 2020 01:30 PM
@Hans Vogelaar 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.
Nov 03 2020 01:37 PM
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?
Nov 03 2020 01:42 PM
@Hans VogelaarIt appears correctly in this version but I won't be able to translate that to the master spreadsheet. I get the same results when I attempt to use this formula in my master report. Any thoughts on what I should look into that might be keeping this from working for me?
Nov 03 2020 01:54 PM
I'd need to know more about your real setup...
Nov 03 2020 01:55 PM
@Hans VogelaarIt is working now. I closed the workbook and reopened it, and now everything is working as needed. Thank you for your help!!
Nov 03 2020 01:05 PM
SolutionAll 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)