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)

 

[T. Valko]

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

Exactly!

 

[mgirvin]

Wow, am I lucky! Getting so many great answers to a post like this is like Christmas with many presents or a kid with a pile of candy!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I learned something awesome from each one of the posts.<o></o>
<o></o>
Thanks pgc01, T. Valko, Aladin, Haseeb, jasonb75, MrKowz!!!<o></o>
<o></o>
Here is what I learned (I hope I assimilated all of your points correctly):
<o></o>
<o></o><o></o>
<o><TABLE style="WIDTH: 525pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=698><COLGROUP><COL style="WIDTH: 231pt; mso-width-source: userset; mso-width-alt: 10951" width=308><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2304" span=6 width=65><TBODY><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 15.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=308>Max</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=65 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=65 align=right>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=65 align=right>35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=65 align=right>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=65 align=right>25</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=308>Scores</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=middle>TRUE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>rad</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=21></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=3>C2 contains formula: =""</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 62.4pt" height=83><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 62.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=83 width=308>In the SUMIF function, the criteria "<>" will return a TRUE for any cell that is "not empty", which means a number, text, logical or formula blank. </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>195</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=3>=SUMIF(B2:G2,"<>",B1:G1)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 46.8pt" height=62><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 46.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=62 width=308>In SUMPRODUCT function, the criteria <>"" will return a TRUE for any cell that is "empty or without a null string".</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>150</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=5>=SUMPRODUCT(--(B2:G2<>""),--(B1:G1))</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=21></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 62.4pt" height=83><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 62.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=83 width=308>In the SUMIF function, the criteria "=" will return a TRUE for any cell that is "empty", which means nothing, not even a formula blanks.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>30</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=3>=SUMIF(B2:G2,"=",B1:G1)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=308>ISBLANK function counts "empty" cells</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=4>=SUMPRODUCT(--ISBLANK(B2:G2))</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 31.2pt" height=42><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 31.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=42 width=308>COUNTBLANK function counts "empty or with a null string"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=3>=COUNTBLANK(B2:G2)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=308>Good for numbers only, including zero.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>85</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=5>=SUM(SUMIF(B2:G2,{">=0","<0"},B1:G1))</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 231pt; HEIGHT: 15.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=308>Good for numbers only, including zero.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>85</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl70 colSpan=4>=SUMIF(B2:G2,"<9.9e307",B1:G1)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 height=21></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70></TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl72 height=21 colSpan=3>** "<>" in SUMIF and <>"" in SUMPRODUCT are not equivalent.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74></TD></TR><TR style="HEIGHT: 14.4pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl75 height=19 colSpan=4>** ISBLANK function: Blank = "empty cell". Help (2010) says: " blank (empty cell)"</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl76></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class=xl76></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77></TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl75 height=21 colSpan=7>** COUNTBLANK function: Blank = "empty or with a null string". Help (2010) says: "Cells with formulas that</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl78 height=21>return "" (empty text) are also counted."</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl75 height=21 colSpan=7>** Paste Special Skip Blanks: Blank = "empty or with a null string". Help (Excel 2010) says: "To avoid</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl78 height=21 colSpan=7>replacing values in your paste area when blank cells occur in the copy area, select Skip blanks"</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid; mso-ignore: colspan" class=xl75 height=21 colSpan=7>** Filter: Blank = "empty or with a null string". Help (Excel 2010) says: " The (Blanks) check box is</TD></TR><TR style="HEIGHT: 15.6pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15.6pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl78 height=21 colSpan=5>available only if the range of cells or table column contains at least one blank cell."</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD></TR></TBODY></TABLE></o>
<o></o>

The team here at the Mr Excel Message Board is awesome!!!

<o></o>