SOLVED
Home

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies