 SOLVED

Highlighted

# Using IF function in a cell range

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
Highlighted

# Re: Using IF function in a cell range

Try this formula:
=SUMPRODUCT(E5:E32*(F5:F32<>”_”))+
SUMPRODUCT(E61:E62*(F61:F62<>”_”))+E64*(F64<>”_”)
Highlighted

# Re: Using IF function in a cell range

Twifoo, it gives error #NAME?
Highlighted

# Re: Using IF function in a cell range

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

# Re: Using IF function in a cell range

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".
Highlighted

# Re: Using IF function in a cell range

It would be better if you attach the sample file so we can scrutinize the possible causes of errors.
Highlighted

# Re: Using IF function in a cell range

sum total is cell E88, sample attach

Highlighted

# Re: Using IF function in a cell range

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.
Highlighted
Solution

# Re: Using IF function in a cell range

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

Highlighted

# Re: Using IF function in a cell range

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

Hi @Twifoo ,

It was not me.

Best Regards,

Naveen

Highlighted

# Re: Using IF function in a cell range

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