Forum Discussion
Solved
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,E...
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
Try this formula:
=SUMPRODUCT(E5:E32*(F5:F32<>”_”))+
SUMPRODUCT(E61:E62*(F61:F62<>”_”))+E64*(F64<>”_”)
=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".
