SOLVED
Home

Sumif not blank

%3CLINGO-SUB%20id%3D%22lingo-sub-354856%22%20slang%3D%22en-US%22%3ESumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354856%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20want%20to%20use%20a%20formula%20to%20ignore%20blank%20cells%20when%20i%20sum%20blank%20spaces%20but%20if%20I%20use%20%22Sum(J6%3AJ9)%22%20it%20will%20give%20me%20a%20%220%22%20even%20if%20there's%20no%20values%20in%20the%20cell%2C%20and%20what%20i'm%20trying%20to%20do%20is%20basically%20this%3A%20%22%3CSTRONG%3Eif(sum(J6%3AJ9)%3D%22%22%2C%22%22%2CSum(J6%3AJ9))%3C%2FSTRONG%3E%22%20but%20the%20formula%20is%20still%20taking%20a%20blank%20space%20as%20%220%22%20so%20it%20will%20give%20me%20again%20%220%22%20when%20i'm%20trying%20to%20ignore%20those%20blank%20spaces.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354856%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-355624%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355624%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%20this%20work%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355558%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355558%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20336px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84115i171EC35C10E24596%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISNA(IFERROR(LARGE(A1%3AA5%2C1)%2CNA()))%2C%22%22%2CSUM(A1%3AA5))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355132%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355132%22%20slang%3D%22en-US%22%3EHello%20Luis%2C%3CBR%20%2F%3EAssuming%20your%20range%20is%20A1%3AA5%20and%20you%20want%20the%20result%20to%20appear%20in%20A6%2C%20your%20formula%20in%20A6%20is%3A%3CBR%20%2F%3E%3DIF(COUNTIF(A%241%3AA5%2C%22%22)%3DROWS(A%241%3AA5)%2C%3CBR%20%2F%3E%22%22%2C%3CBR%20%2F%3ESUM(A%241%3AA5))%3CBR%20%2F%3ECorollary%20to%20my%20earlier%20statement%2C%20Blank%20Cells%20are%20not%2C%20but%20are%20counted%20as%2C%20Empty%20Text%20(%22%22)%2C%20such%20that%20this%20formula%2C%20although%20longer%2C%20achieves%20the%20same%20result%3A%3CBR%20%2F%3E%3DIF(SUMPRODUCT(--ISBLANK(A%241%3AA5))%3DROWS(A%241%3AA5)%2C%3CBR%20%2F%3E%22%22%2C%3CBR%20%2F%3ESUM(A%241%3AA5))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355054%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20915px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83995i0EE53616CB8D6983%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22example.PNG%22%20title%3D%22example.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355025%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20405px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83986iB12715A5E6F72D39%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354912%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354912%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Twifoo%2C%3C%2FP%3E%3CP%3EThanks%20for%20your%20fast%20response%2C%20the%20thing%20is%20that%2C%20if%20i%20use%20that%20formula%2C%20there's%20sometimes%20when%20the%20number%20might%20be%20%220%22%2C%20and%20that's%20ok%20for%20me%2C%20but%20if%20the%20cell%20is%20blank%2C%20the%20result%20of%20that%20%22sum%22%20formula%20is%20still%20giving%20me%20%220%22.%3C%2FP%3E%3CP%3EImagine%20inside%20the%20bottom%20cell%20is%20the%20%22sum%22%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20ok%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20also%20ok.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20not%20ok.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354879%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354879%22%20slang%3D%22en-US%22%3E%3CPRE%3E%3Dif(sum(J6%3AJ9)%3D0%2C%22%22%2CSum(J6%3AJ9))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354878%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20not%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354878%22%20slang%3D%22en-US%22%3EHello%20Luis%2C%3CBR%20%2F%3EIt%20appears%20that%20you%20want%20the%20result%20to%20appear%20as%20an%20EMPTY%20TEXT%20(%E2%80%9C%E2%80%9D)%2C%20if%20the%20sum%20range%20solely%20consists%20of%20blank%20cells.%20This%20formula%20will%20return%20what%20you%20want%3A%3CBR%20%2F%3E%3DIF(SUM(J6%3AJ9)%2C%3CBR%20%2F%3ESUM(J6%3AJ9)%2C%3CBR%20%2F%3E%E2%80%9C%E2%80%9D)%3CBR%20%2F%3ENote%20that%20a%200%20result%20of%20the%20logical_test%20argument%20of%20the%20IF%20function%20is%20equivalent%20to%20FALSE.%20Conversely%2C%20a%20non-zero%20result%20thereof%20is%20equivalent%20to%20TRUE.%3CBR%20%2F%3EIn%20plain%20words%2C%20the%20formula%20means%20that%20if%20the%20sum%20is%20not%200%2C%20return%20that%20sum%3B%20otherwise%2C%20return%20an%20EMPTY%20TEXT%20(%E2%80%9C%E2%80%9D).%3CBR%20%2F%3EFor%20distinction%2C%20empty%20text%20is%20text%20with%200%20length%20while%20blank%20cell%20is%20cell%20without%20any%20content.%20Empty%20text%20is%20not%2C%20but%20is%20counted%20as%2C%20blank.%3C%2FLINGO-BODY%3E
Luis BERNAL
New Contributor

Hello!

I want to use a formula to ignore blank cells when i sum blank spaces but if I use "Sum(J6:J9)" it will give me a "0" even if there's no values in the cell, and what i'm trying to do is basically this: "if(sum(J6:J9)="","",Sum(J6:J9))" but the formula is still taking a blank space as "0" so it will give me again "0" when i'm trying to ignore those blank spaces.

 

Any ideas?

8 Replies
Hello Luis,
It appears that you want the result to appear as an EMPTY TEXT (“”), if the sum range solely consists of blank cells. This formula will return what you want:
=IF(SUM(J6:J9),
SUM(J6:J9),
“”)
Note that a 0 result of the logical_test argument of the IF function is equivalent to FALSE. Conversely, a non-zero result thereof is equivalent to TRUE.
In plain words, the formula means that if the sum is not 0, return that sum; otherwise, return an EMPTY TEXT (“”).
For distinction, empty text is text with 0 length while blank cell is cell without any content. Empty text is not, but is counted as, blank.
=if(sum(J6:J9)=0,"",Sum(J6:J9))

Hi Twifoo,

Thanks for your fast response, the thing is that, if i use that formula, there's sometimes when the number might be "0", and that's ok for me, but if the cell is blank, the result of that "sum" formula is still giving me "0".

Imagine inside the bottom cell is the "sum" formula.

 

This is ok

1
1
0
0
2

 

This is also ok.

 
 
 
 
 

 

This is not ok.

 
 
 
 
0

image.png

example.PNG

Hello Luis,
Assuming your range is A1:A5 and you want the result to appear in A6, your formula in A6 is:
=IF(COUNTIF(A$1:A5,"")=ROWS(A$1:A5),
"",
SUM(A$1:A5))
Corollary to my earlier statement, Blank Cells are not, but are counted as, Empty Text (""), such that this formula, although longer, achieves the same result:
=IF(SUMPRODUCT(--ISBLANK(A$1:A5))=ROWS(A$1:A5),
"",
SUM(A$1:A5))
Solution

Another variant

image.png

=IF(ISNA(IFERROR(LARGE(A1:A5,1),NA())),"",SUM(A1:A5))
Thank you so much! this work
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