SOLVED

Using IF function in a cell range

Copper Contributor

I have an IF function I am trying to use to sum a cell range but I am getting an error (#VALUE!). My formula is

=SUM(E5:E32)+(IF(F5:F32="_",0,E5:E32))+(E61)+(IF(F61="_",0,E61))+(E62)+(IF(F62="_",0,E62))+(E64)+(IF(F64="_",0,E64))

This is what I am trying to accomplish, I am summing cells E5:E32,E61,E62,E64 in cell E88, the if function needs to return 0, if the adjacent cells F has a value of "_" . Any suggestions would be greatly appreciated.

12 Replies
Try this formula:
=SUMPRODUCT(E5:E32*(F5:F32<>”_”))+
SUMPRODUCT(E61:E62*(F61:F62<>”_”))+E64*(F64<>”_”)
Twifoo, it gives error #NAME?
Perhaps you need to modify the values in Column F from underscore (_) to 0. Also, modify our formula by replacing underscore (_) with 0. Excel might be thinking that “_” is a defined name, so it is returning a #NAME? error.
So, my data validation list for cell "F" is (Y,N,_). I changed "_" to "0" and it displayed as "#" when I selected in my drop down list, I tried different values to replace the "_", even another letter "E". I still get the same error. The "Y" and "N" is for another sum formula which is =(C79+K79)+(IF(F79="N",0,E79))+(IF(H79="N",0,G79))+(IF(J79="N",0,I79)), and it works correctly. In this the current sum total I am trying to apply the range to "0" sum for "Y" and "N".
It would be better if you attach the sample file so we can scrutinize the possible causes of errors.

sum total is cell E88, sample attach

I suggest that your drop-down list for Column F should be limited to Y,N only.l The underscore "_" therein serves no useful purpose.
best response confirmed by dportalis_123 (Copper Contributor)
Solution

Hi @dportalis_123 ,

 

E89 cell highlighted with green color.it's fulfill your requirement. 

File is attached for your reference.

If any query remaining let me know

 

Best regards,

Naveen

I see nothing wrong with the formula. It returns no #NAME? error as you claimed earlier.

Hi @Twifoo ,

 

It was not me. 

 

Best Regards,

Naveen

Naveen, thank you, this works perfectly, solved my needs. I appreciate everyone that participated.

Hi @dportalis_123 ,

 

Happy to help you.

 

Best regards,

Naveen

1 best response

Accepted Solutions
best response confirmed by dportalis_123 (Copper Contributor)
Solution

Hi @dportalis_123 ,

 

E89 cell highlighted with green color.it's fulfill your requirement. 

File is attached for your reference.

If any query remaining let me know

 

Best regards,

Naveen

View solution in original post