SOLVED
Home

Excel- SUM function not working with =IF(COUNTIFS function

%3CLINGO-SUB%20id%3D%22lingo-sub-656547%22%20slang%3D%22en-US%22%3EExcel-%20SUM%20function%20not%20working%20with%20%3DIF(COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656547%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20am%20trying%20to%20find%20the%20Sum%20of%20a%20column%20that%20has%20given%20me%20the%20output%20of%20either%200%20or%201%20using%20the%20formula%26nbsp%3B%3DIF(COUNTIFS(B22%3AW22%2C%22np%22)*1%2C%221%22%2C%220%22)-%20I%20have%20attached%20the%20Excel%20spreadsheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJames%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-656547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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-656594%22%20slang%3D%22en-US%22%3ERe%3A%20Excel-%20SUM%20function%20not%20working%20with%20%3DIF(COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351473%22%20target%3D%22_blank%22%3E%40James09%3C%2FA%3E%26nbsp%3B%2C%20use%20numbers%2C%20not%20texts%3C%2FP%3E%0A%3CPRE%3E%3DIF(COUNTIFS(B4%3AW4%2C%22np%22)%2C1%2C0)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-656606%22%20slang%3D%22en-US%22%3ERe%3A%20Excel-%20SUM%20function%20not%20working%20with%20%3DIF(COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351473%22%20target%3D%22_blank%22%3E%40James09%3C%2FA%3E%26nbsp%3B%2C%20or%2C%20as%20variant%3C%2FP%3E%0A%3CPRE%3E%3D--(COUNTIFS(B4%3AW4%2C%22np%22)%26gt%3B0)%3C%2FPRE%3E%0A%3CP%3Edouble%20dash%20converts%20logical%20to%20number%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-656800%22%20slang%3D%22en-US%22%3ERe%3A%20Excel-%20SUM%20function%20not%20working%20with%20%3DIF(COUNTIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFantastic%2C%3C%2FP%3E%3CP%3EThank%20you%20Very%20Much%20I%20had%20no%20idea%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJames%3C%2FP%3E%3C%2FLINGO-BODY%3E
James09
New Contributor

Hi I am trying to find the Sum of a column that has given me the output of either 0 or 1 using the formula =IF(COUNTIFS(B22:W22,"np")*1,"1","0")- I have attached the Excel spreadsheet

 

Many Thanks

 

James

3 Replies

@James09 , use numbers, not texts

=IF(COUNTIFS(B4:W4,"np"),1,0)
Solution

@James09 , or, as variant

=--(COUNTIFS(B4:W4,"np")>0)

double dash converts logical to number

 

@Sergei Baklan 

Fantastic,

Thank you Very Much I had no idea

 

James

Related Conversations