SOLVED

Using SUMIF to exclude values

%3CLINGO-SUB%20id%3D%22lingo-sub-260294%22%20slang%3D%22en-US%22%3EUsing%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260294%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20use%20SUMIF%2C%20if%20possible%2C%20to%20add%20up%20a%20column%20but%20to%20exclude%20values%20when%20a%20corresponding%20cell%20contains%20the%20text%20%22In%20Flight%20Relief%22.%20Can%20anybody%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-260294%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-260886%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260886%22%20slang%3D%22en-US%22%3E%3CP%3Eglad%20to%20help..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260816%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260816%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20Lorenzo.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20worked%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260568%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260568%22%20slang%3D%22en-US%22%3Ecan%20also%20reference%20a%20cell%20(Z2)%3A%3CBR%20%2F%3E%3DSUMIF(Z2%3AZ10%2C%22%26lt%3B%26gt%3B%22%26amp%3BZ2%2CM2%3AM10)%3CBR%20%2F%3Ewhere%20Z2%20is%20the%20controlling%20text.%3CBR%20%2F%3Ebe%20sure%20that%20the%20text%20is%20consistent%20in%20all%20controlling%20cells%20-%20otherwise%20it%20won't%20function%20properly.%3CBR%20%2F%3E%22In%20Flight%20Relief%22%20is%20not%20sames%20as%20%22In%20Flight%20Relief%22%20nor%20%22Inflightrelief%22..%3CBR%20%2F%3EIf%20Z2%20is%20deleted%20then%20the%20sum%20will%20be%20zero....%20because%20the%20controlling%20cell%20is%20now%20blank.%3CBR%20%2F%3EHTH%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260565%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260565%22%20slang%3D%22en-US%22%3E%3DSUMIF(Z2%3AZ10%2C%22%26lt%3B%26gt%3BIn%20flight%20Relief%22%2CM2%3AM10)%3CBR%20%2F%3Echange%20the%20range%20%26amp%3B%20text%20to%20suit...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260563%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260563%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20I%20solved%20your%20sumif%20-%20pls%20see%20attached%20file%3C%2FP%3E%3CP%3Ethanks..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260561%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260561%22%20slang%3D%22en-US%22%3Epress%20ALT%20F11%20to%20see%20the%20code%20-%20you%20can%20change%20any%20variables%20to%20suit..%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260560%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260560%22%20slang%3D%22en-US%22%3E%3CP%3EI%20prepared%20a%20sample%20-%3C%2FP%3E%3CP%3Esee%20if%20you%20can%20work%20something%20out%20of%20it.%3C%2FP%3E%3CP%3Ethanks..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260557%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260557%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20response%20Lorenzo.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20my%20query%20may%20have%20been%20unclear.%20If%20the%20column%20I%20want%20to%20sum%20is%20M%20I%20would%20like%20the%20function%20to%20SUM%2C%20but%20exclude%20values%20within%20column%20M%20when%20there%20is%20specific%20text%20in%20a%20corresponding%20cell%20(let%E2%80%99s%20say%20column%20Z).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20use%20SUMIF%20to%20exclude%20the%20values%20when%20the%20corresponding%20cell%20has%20any%20unspecified%20text%20%26nbsp%3Bbut%20I%20would%20like%20to%20exclude%20those%20cell%20with%20specific%20text.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260522%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20SUMIF%20to%20exclude%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260522%22%20slang%3D%22en-US%22%3Eif%20you%20are%20adding%20a%20column%20use%20%3DSUM(range)%3CBR%20%2F%3Ethis%20will%20sum%20only%20the%20numbers%20excluding%20strings%20or%20text%20cell..%3CBR%20%2F%3EHTH%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Guys,

 

I would like to use SUMIF, if possible, to add up a column but to exclude values when a corresponding cell contains the text "In Flight Relief". Can anybody help?

10 Replies
if you are adding a column use =SUM(range)
this will sum only the numbers excluding strings or text cell..
HTH

Thanks for your response Lorenzo.

 

I think my query may have been unclear. If the column I want to sum is M I would like the function to SUM, but exclude values within column M when there is specific text in a corresponding cell (let’s say column Z).

 

I know how use SUMIF to exclude the values when the corresponding cell has any unspecified text  but I would like to exclude those cell with specific text. 

 

Thanks again. 

I prepared a sample -

see if you can work something out of it.

thanks..

 

press ALT F11 to see the code - you can change any variables to suit..

I think I solved your sumif - pls see attached file

thanks..

=SUMIF(Z2:Z10,"<>In flight Relief",M2:M10)
change the range & text to suit...
best response confirmed by Bradley Peters (New Contributor)
Solution
can also reference a cell (Z2):
=SUMIF(Z2:Z10,"<>"&Z2,M2:M10)
where Z2 is the controlling text.
be sure that the text is consistent in all controlling cells - otherwise it won't function properly.
"In Flight Relief" is not sames as "In Flight Relief" nor "Inflightrelief"..
If Z2 is deleted then the sum will be zero.... because the controlling cell is now blank.
HTH

Thank you very much Lorenzo.

 

This formula worked for me.

 

 

glad to help..

@Lorenzo Kim thanks, thats very helpful.

How about if instead of excluding I want to only include field. 

So base on the example I will be only including the "In flight Relief" in my sum calculation.

 

Maria