SOLVED

COUNTIFS function help needed

%3CLINGO-SUB%20id%3D%22lingo-sub-1851023%22%20slang%3D%22en-US%22%3ECOUNTIFS%20function%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851023%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20COUNTIFS%20but%20it%20is%20not%20working%20when%20I%20add%20the%20second%20condition%2C%20despite%20being%20able%20to%20visually%20confirm%20there%20are%20cells%20that%20meet%20both%20conditions%20I've%20identified.%26nbsp%3B%20In%20the%20attached%20example%2C%20I%20need%20to%20count%20the%20number%20of%20rows%20in%20spreadsheet%20%22Data%22%20for%20which%20the%20contents%20of%20column%20F%20match%20A2%20in%20spreadsheet%20%22Reference%22.%26nbsp%3B%20This%20function%20works%20fine%20with%20COUNTIF%20and%20results%20in%208%20as%20shown%20in%20K2.%26nbsp%3B%20When%20I%20add%20a%20condition%20that%20asks%20Excel%20to%20then%20count%20the%20number%20of%20rows%20with%20%221%22%20in%20column%20M%2C%20I%20get%20a%20%23VALUE!%20error.%26nbsp%3B%20The%20formula%20below%20is%20used%20in%20L2%20in%20the%20Reference%20tab%20of%20the%20attached%20Example%20report.%26nbsp%3B%20What%20am%20I%20doing%20wrong%3F%26nbsp%3B%20How%20do%20I%20get%20Excel%20to%20count%20these%20for%20me%3F%26nbsp%3B%20I%20have%20a%20report%20with%20thousands%20of%20rows%20of%20these%20data%20that%20I%20need%20to%20aggregate%20and%20don't%20have%20time%20to%20use%20a%20pivot%20table%20for%20every%20item.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Data!A%3AF%2CA2%2CData!A%3AM%2C%221%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1851023%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1851042%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20function%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F856493%22%20target%3D%22_blank%22%3E%40kselinko%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAll%20Criteria%20ranges%20of%20COUNTIFS%20must%20have%20the%20same%20size%20and%20shape.%20But%20A%3AF%20contains%206%20columns%20while%20A%3AM%20contains%2013%20columns.%20Use%20this%20instead%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(Data!F%3AF%2CA2%2CData!M%3AM%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1851046%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20function%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3EThat%20fixed%20the%20value%20error%20but%20now%20it's%20just%20returning%20%220%22%20when%20we%20can%20see%20there%20are%208%20rows%20with%20data%20in%20columns%20F%20and%20M%20that%20meet%20the%20criteria.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

 

8 Replies
Highlighted
Best Response confirmed by kselinko (Occasional Contributor)
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)
Highlighted

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

Highlighted

@kselinko 

It works for me. Perhaps you have to recalculate?

S0004.png

Highlighted

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

 

kselinko_0-1604438586263.png

 

Highlighted

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

Highlighted

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

Highlighted

@kselinko 

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

Highlighted

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