Forum Discussion
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 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")
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)
8 Replies
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)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.
