Not Blank In SUMIF function

[mgirvin]

Dear Smartest Excelers Around,

Below are two formulas for adding when cells are not blank. Does anyone know how to get the SUMIF to work? The SUMPRODUCT works, but not the SUMIF.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	Test1	Test2	Test3	Test4	Test5	Test6	Test7	Test8
2	Max	100	100	100	50	50	50	100	200
3	Name1	85	91	100	41		39
4
5	Total Possible	750	400
...

Excel 2010

Cell Formulas
Range	Formula
B5	=SUMIF(B3:I3,"<>""",B2:I2)
C5	=SUMPRODUCT(--(B3:I3<>""),B2:I2)

 

[MrKowz]

Just use:

=SUMIF(B3:I3,"<>",B2:I2)

 

[mgirvin]

Toatlly awesome, MrKowz!!

All these years, I have been switching over to SUMPRODUCT whenever I get into this situation and it is as simple as "<>".

But how does Excel see that criteria? Does it see it as only "Not Blank", or is the category broader than that?

 

[MrKowz]

It has the condition that equivalent to saying <>""

So it will return true for any cell that is blank or contains the null character.

As for your SUMPRODUCTs, I'd highly reccomend switching all of those over, as SUMIF is much more processor effecient.

 

[mgirvin]

MrKowz,
Cool, got it “<>” is same as <>””. I totally agree about the SUMIF. I have done numerous timing formula tests over the years and am always amazed at how much faster SUMIF/COUNTIF are over SUMPRODUCT, SUM(IF(… and other similar formulas. That is why I am so happy that you gave me a new trick for my Excel tool kit. Thanks!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[jasonb75]

Just a quick pointer Mike, that only works with empty cells, formula blanks can give undesirable results.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Name</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test1</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test2</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test3</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test4</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test5</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test6</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test7</TD><TD style="BACKGROUND-COLOR: #003366; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">Test8</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Max</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">200</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">85</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">91</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">41</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">39</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD style="TEXT-ALIGN: right">85</TD><TD style="TEXT-ALIGN: right">91</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">41</TD><TD></TD><TD style="TEXT-ALIGN: right">39</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD style="TEXT-ALIGN: right">750</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B4:I4</TD><TD>=IF(B3>0,B3,"")</TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD>B6</TD><TD>=SUMIF(B4:I4,"<>",B2:I2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Haseeb Avarakkan]

Hello,

I think this would work, If the numbers are >0, <0

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Test1</td><td style=";">Test2</td><td style=";">Test3</td><td style=";">Test4</td><td style=";">Test5</td><td style=";">Test6</td><td style=";">Test7</td><td style=";">Test8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Max</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Name1</td><td style="text-align: right;;">85</td><td style="text-align: right;;">91</td><td style="text-align: right;;">100</td><td style="text-align: right;;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;">39</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">85</td><td style="text-align: right;;">91</td><td style="text-align: right;;">100</td><td style="text-align: right;;">41</td><td style=";"></td><td style="text-align: right;;">39</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">750</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=IF(<font color="Blue">B3>0,B3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=SUMIF(<font color="Blue">B4:I4,"<>",B2:I2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=SUM(<font color="Blue">SUMIF(<font color="Red">B4:I4,{">0","<0"},B2:I2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
It won't work with texts.

 

[Aladin Akyurek]

Non-blank as criterion:

SUMIF(CriterionRange,"<>",SumRange)

Blank as criterion:

SUMIF(CriterionRange,"=",SumRange)

SumIf and CountIf both process (a) range objects, while Sum/If, Max/If, etc and SumProduct process (b) array objects. The latter type of objects apparently require more processing time.

 

[T. Valko]

Non-blank as criterion:

SUMIF(CriterionRange,"<>",SumRange)

Blank as criterion:

SUMIF(CriterionRange,"=",SumRange)

SumIf and CountIf both process (a) range objects, while Sum/If, Max/If, etc and SumProduct process (b) array objects. The latter type of objects apparently require more processing time.

Sometimes there's confusion about the difference between "blank" and empty.

Blank and empty aren't necessarily the same thing.

If a cell contains a formula that returns the empty text string "" then that cell IS NOT EMPTY.

<> means "is not empty"
= means "is empty"

So:

SUMIF(range,"<>",sum_range) will include cells that contain formula blanks

SUMIF(range,"=",sum_range) will exclude cells that contain formula blanks

Another way to write that formula:

=SUMIF(B4:I4,"<1E100",B2:I2)

Both cells that contain formula blanks and empty cells are ignored.

 

[pgc01]

Sometimes there's confusion about the difference between "blank" and empty.

Unfortunately that confusion starts in excel documentation, it's a mess in the case or the word Blank.

There is a terminology error in the the excel documentation that makes this problem confusing: the word Blank is used with 2 different meanings.

The 2 different meanings of the word Blank in the help:

1 - A Blank cell can mean an empty cell. This is the case with the function IsBlank() and the Paste Special with Skip Blanks

2 - A Blank cell can mean an empty cell or a cell with a null string. This is the case with the function CountBlank() and in the Autofilter when you select Blanks in the dropdown

This means that it is always confusing if one uses the term Blank. Maybe the best would be to avoid it and use instead for ex. "empty" or "empty or with a null string".