SOLVED

Using IF function in a cell range

%3CLINGO-SUB%20id%3D%22lingo-sub-357634%22%20slang%3D%22en-US%22%3EUsing%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357634%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20IF%20function%20I%20am%20trying%20to%20use%20to%20sum%20a%20cell%20range%20but%20I%20am%20getting%20an%20error%20(%23VALUE!).%20My%20formula%20is%3C%2FP%3E%3CP%3E%3DSUM(E5%3AE32)%2B(IF(F5%3AF32%3D%22_%22%2C0%2CE5%3AE32))%2B(E61)%2B(IF(F61%3D%22_%22%2C0%2CE61))%2B(E62)%2B(IF(F62%3D%22_%22%2C0%2CE62))%2B(E64)%2B(IF(F64%3D%22_%22%2C0%2CE64))%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20am%20trying%20to%20accomplish%2C%20I%20am%20summing%20cells%20E5%3AE32%2CE61%2CE62%2CE64%20in%20cell%20E88%2C%20the%20if%20function%20needs%20to%20return%200%2C%20if%20the%20adjacent%20cells%20F%20has%20a%20value%20of%20%22_%22%20.%20Any%20suggestions%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-357634%22%20slang%3D%22en-US%22%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-358729%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358729%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F291634%22%20target%3D%22_blank%22%3E%40dportalis_123%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHappy%20to%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358279%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358279%22%20slang%3D%22en-US%22%3ENaveen%2C%20thank%20you%2C%20this%20works%20perfectly%2C%20solved%20my%20needs.%20I%20appreciate%20everyone%20that%20participated.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358134%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358134%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20not%20me.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358123%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358123%22%20slang%3D%22en-US%22%3EI%20see%20nothing%20wrong%20with%20the%20formula.%20It%20returns%20no%20%23NAME%3F%20error%20as%20you%20claimed%20earlier.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358118%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358118%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F291634%22%20target%3D%22_blank%22%3E%40dportalis_123%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE89%20cell%20highlighted%20with%20green%20color.it's%20fulfill%20your%20requirement.%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20is%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3EIf%20any%20query%20remaining%20let%20me%20know%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358072%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358072%22%20slang%3D%22en-US%22%3EI%20suggest%20that%20your%20drop-down%20list%20for%20Column%20F%20should%20be%20limited%20to%20Y%2CN%20only.l%20The%20underscore%20%22_%22%20therein%20serves%20no%20useful%20purpose.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357763%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357763%22%20slang%3D%22en-US%22%3E%3CP%3Esum%20total%20is%20cell%20E88%2C%20sample%20attach%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357748%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357748%22%20slang%3D%22en-US%22%3EIt%20would%20be%20better%20if%20you%20attach%20the%20sample%20file%20so%20we%20can%20scrutinize%20the%20possible%20causes%20of%20errors.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357737%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357737%22%20slang%3D%22en-US%22%3ESo%2C%20my%20data%20validation%20list%20for%20cell%20%22F%22%20is%20(Y%2CN%2C_).%20I%20changed%20%22_%22%20to%20%220%22%20and%20it%20displayed%20as%20%22%23%22%20when%20I%20selected%20in%20my%20drop%20down%20list%2C%20I%20tried%20different%20values%20to%20replace%20the%20%22_%22%2C%20even%20another%20letter%20%22E%22.%20I%20still%20get%20the%20same%20error.%20The%20%22Y%22%20and%20%22N%22%20is%20for%20another%20sum%20formula%20which%20is%20%3D(C79%2BK79)%2B(IF(F79%3D%22N%22%2C0%2CE79))%2B(IF(H79%3D%22N%22%2C0%2CG79))%2B(IF(J79%3D%22N%22%2C0%2CI79))%2C%20and%20it%20works%20correctly.%20In%20this%20the%20current%20sum%20total%20I%20am%20trying%20to%20apply%20the%20range%20to%20%220%22%20sum%20for%20%22Y%22%20and%20%22N%22.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357724%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357724%22%20slang%3D%22en-US%22%3EPerhaps%20you%20need%20to%20modify%20the%20values%20in%20Column%20F%20from%20underscore%20(_)%20to%200.%20Also%2C%20modify%20our%20formula%20by%20replacing%20underscore%20(_)%20with%200.%20Excel%20might%20be%20thinking%20that%20%E2%80%9C_%E2%80%9D%20is%20a%20defined%20name%2C%20so%20it%20is%20returning%20a%20%23NAME%3F%20error.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357717%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357717%22%20slang%3D%22en-US%22%3ETwifoo%2C%20it%20gives%20error%20%23NAME%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357710%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20function%20in%20a%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357710%22%20slang%3D%22en-US%22%3ETry%20this%20formula%3A%3CBR%20%2F%3E%3DSUMPRODUCT(E5%3AE32*(F5%3AF32%26lt%3B%26gt%3B%E2%80%9D_%E2%80%9D))%2B%3CBR%20%2F%3ESUMPRODUCT(E61%3AE62*(F61%3AF62%26lt%3B%26gt%3B%E2%80%9D_%E2%80%9D))%2BE64*(F64%26lt%3B%26gt%3B%E2%80%9D_%E2%80%9D)%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted
Try this formula:
=SUMPRODUCT(E5:E32*(F5:F32<>”_”))+
SUMPRODUCT(E61:E62*(F61:F62<>”_”))+E64*(F64<>”_”)
Twifoo, it gives error #NAME?
Highlighted
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
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
It would be better if you attach the sample file so we can scrutinize the possible causes of errors.
Highlighted

sum total is cell E88, sample attach

Highlighted
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
Best Response confirmed by dportalis_123 (Occasional 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

Highlighted
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
Naveen, thank you, this works perfectly, solved my needs. I appreciate everyone that participated.
Highlighted

Hi @dportalis_123 ,

 

Happy to help you.

 

Best regards,

Naveen