COUNTIFS function help needed

[kselinko]

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")

[#2 · Accepted solution]

@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)

[#3]

@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.

[#4]

@kselinko 

It works for me. Perhaps you have to recalculate?

 

[#5]

@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.

[#6]

@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?

[#7]

@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?

[#8]

@kselinko 

I'd need to know more about your real setup...

[#9]

@Hans VogelaarIt is working now.  I closed the workbook and reopened it, and now everything is working as needed.  Thank you for your help!!