Formula to sum and exclude

%3CLINGO-SUB%20id%3D%22lingo-sub-1604646%22%20slang%3D%22en-US%22%3EFormula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1604646%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20that%20will%20look%20up%20customer%20name%20and%20amount%20from%20one%20sheet%20and%20match%20it%20the%20customer%20name%20on%20a%20different%20sheet.%20I%20would%20like%20it%20to%20also%20not%20include%20customers%20that%20have%20a%200.00%20amount.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20have%20so%20far%3A%26nbsp%3B%3DSUMIF('COGS%20Breakdown'!%24A%3A%24A%2C'Import%20Passportal%20SI'!G3%2C'COGS%20Breakdown'!%24O%3A%24O)%3C%2FP%3E%3CP%3EI%20just%20need%20the%20additional%20formula%20for%20to%20exclude%20values%20that%20are%200%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1605307%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1605307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767363%22%20target%3D%22_blank%22%3E%40MelStewart9655%3C%2FA%3E%26nbsp%3B%2C%20it%20would%20be%20better%20if%20you%20attach%20a%20file%20with%20example%20data.%20Reading%20your%20question%2C%20it%20creates%20a%20doubt%20about%20intent%20and%20aim%2C%20since%2C%20you%20are%20trying%20to%20achieve%20a%20sum%20and%20want%20to%20exclude%200%20(zeroes)%2C%20so%20how%20the%20result%20of%20sum%20would%20get%20affected%20whether%20or%20not%20zeroes%20are%20included%3F%20Obviously%2C%20we're%20not%20able%20to%20understand%20the%20query%2C%20for%20which%2C%20an%20attachment%20with%20example%20data%20(and%20probably%20a%20typed%2C%20manually%20formulated%20result%20that%20you%20expect)%20would%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1605494%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1605494%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%2F767363%22%20target%3D%22_blank%22%3E%40MelStewart9655%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20already%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%2C%20you%20need%20to%20provide%20more%20information%20for%20clarity.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcluding%20and%20including%20zero%20should%20produce%20the%20same%20result.%20Also%2C%20you%20stated%20that%20you%20wanted%20to%20look%20up%20some%20customers%20from%20one%20sheet%2C%20and%20compare%20to%20another%2C%20I%20am%20wondering%20how%20this%20can%20be%20achieved%20by%20using%20SUMIF.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20share%20the%20file%20after%20removing%20the%20confidential%20information%2C%20that%20will%20be%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608356%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608356%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%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20my%20excel%20spreadsheet.%26nbsp%3B%20I%20use%20this%20to%20import%20COGS%20using%20this%20sheet.%26nbsp%3B%20I%20basically%20don't%20want%20a%20large%20list%20of%20companies%20that%20have%200%20amount%2C%20as%20this%20will%20be%20part%20of%20the%20journal%20entry%20to%20be%20imported.%26nbsp%3B%20%26nbsp%3BSo%20if%20I%20am%20able%20to%20somehow%20exclude%20companies%20with%20a%200%20amount%2C%20it%20will%20be%20much%20cleaner.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608463%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767363%22%20target%3D%22_blank%22%3E%40MelStewart9655%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20can%20still%20use%20the%20sheet.%20Just%20filter%20out%20those%20items%20with%20zero.%20Select%20all%20the%20items%20and%20then%20click%20on%20Data%20%26gt%3B%26gt%3BFilter.%20Then%20click%20on%20column%20K%20header%20to%20uncheck%20zero%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wumolad_1-1598287103931.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214352i0E3E5EEDF7CE02E7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22wumolad_1-1598287103931.png%22%20alt%3D%22wumolad_1-1598287103931.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20click%20ok.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20click%20on%20Ctrl%20%2B%20G%2C%20click%20on%20special%20and%20select%20visible%20cells%20only%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wumolad_0-1598286960065.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214351iF27D0EC4A409A316%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22wumolad_0-1598286960065.png%22%20alt%3D%22wumolad_0-1598286960065.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20allows%20you%20to%20copy%20only%20the%20copied%20items%20which%20you%20can%20then%20paste%20to%20another%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sample%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608653%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767363%22%20target%3D%22_blank%22%3E%40MelStewart9655%3C%2FA%3E%26nbsp%3B%2C%20In%20attached%20file%2C%20i%20used%20your%20already%20made%20sheet%20as%20a%20temporary%20calculation%2C%20and%20out%20of%20it%2C%20filtered%20out%20only%20the%20non-zero%20companies%20as%20you%20desire.%20It%20gets%20filtered%20automatically.%20Only%20take%20care%20to%20drag%20down%20the%20formulas%20enough%20so%20that%20the%20formula%20region%20is%20more%20down%20below%20the%20data%20region.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22amit_bhola_0-1598292060686.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214371i86751420C99CE0A5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22amit_bhola_0-1598292060686.png%22%20alt%3D%22amit_bhola_0-1598292060686.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609041%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609041%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20for%20your%20advice!%26nbsp%3B%20I%20appreciate%20it!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609046%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609046%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20is%20a%20great%20idea!%26nbsp%3B%20thank%20you!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610100%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20sum%20and%20exclude%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610100%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767363%22%20target%3D%22_blank%22%3E%40MelStewart9655%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20are%20welcome%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

I am trying to create a formula that will look up customer name and amount from one sheet and match it the customer name on a different sheet. I would like it to also not include customers that have a 0.00 amount.  

This is what I have so far: =SUMIF('COGS Breakdown'!$A:$A,'Import Passportal SI'!G3,'COGS Breakdown'!$O:$O)

I just need the additional formula for to exclude values that are 0

8 Replies
Highlighted

@MelStewart9655 , it would be better if you attach a file with example data. Reading your question, it creates a doubt about intent and aim, since, you are trying to achieve a sum and want to exclude 0 (zeroes), so how the result of sum would get affected whether or not zeroes are included? Obviously, we're not able to understand the query, for which, an attachment with example data (and probably a typed, manually formulated result that you expect) would help!

Highlighted

Hi @MelStewart9655 

 

As already suggested by @amit_bhola, you need to provide more information for clarity.

 

Excluding and including zero should produce the same result. Also, you stated that you wanted to look up some customers from one sheet, and compare to another, I am wondering how this can be achieved by using SUMIF.

 

If you can share the file after removing the confidential information, that will be useful.

 

Cheers

 

Highlighted

Hi @wumolad 

 

Please see attached my excel spreadsheet.  I use this to import COGS using this sheet.  I basically don't want a large list of companies that have 0 amount, as this will be part of the journal entry to be imported.   So if I am able to somehow exclude companies with a 0 amount, it will be much cleaner. 

Highlighted

@MelStewart9655 

 

I think you can still use the sheet. Just filter out those items with zero. Select all the items and then click on Data >>Filter. Then click on column K header to uncheck zero:

 

wumolad_1-1598287103931.png

 

and click ok.

 

Then click on Ctrl + G, click on special and select visible cells only:

 

 

wumolad_0-1598286960065.png

 

This allows you to copy only the copied items which you can then paste to another sheet.

 

I have attached a sample for you.

 

Hope this helps.

 

Cheers

Highlighted

@MelStewart9655 , In attached file, i used your already made sheet as a temporary calculation, and out of it, filtered out only the non-zero companies as you desire. It gets filtered automatically. Only take care to drag down the formulas enough so that the formula region is more down below the data region.

 

amit_bhola_0-1598292060686.png

 

Highlighted

thank you for your advice!  I appreciate it! @wumolad 

Highlighted

this is a great idea!  thank you! @amit_bhola 

Highlighted
@MelStewart9655

You are welcome