 • 512K Members
• 8,151 Online
• 609K Conversations
SOLVED

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

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

# Re: Excel- SUM function not working with =IF(COUNTIFS function

@James09 , use numbers, not texts

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

# Re: Excel- SUM function not working with =IF(COUNTIFS function

@James09 , or, as variant

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

double dash converts logical to number

# Re: Excel- SUM function not working with =IF(COUNTIFS function

Fantastic,

Thank you Very Much I had no idea

James

Related Conversations
Extracting data from a list without duplicate (repeating entry)
MrNobody in Excel on
6 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
COUNTIF not working
sme527 in Excel on
10 Replies
Multiple Selections Drop Down List (used a VBA code) & CountIfs
Teeah in Excel on
0 Replies