Another no value cell issue came up last week. This time about how to reference a blank cell when making a formula. This is done by using open and close quotes with nothing between them. No space, no 0, no nothing. It is just “”.
Example: You have values assigned to groups one, two, and three, and some values that have no group designation. You need to sum the total values for each group. To sum the values related to no group or a blank space you would need to reference the blank cells in your formula with double quotation marks, “”. That is quotes with nothing between them.
Function Example: =sumif(C4:C12,””,B4:B12)
For further information on the SumIf function in Excel watch the Semi-Logical Functions video on our Microsoft Office Excel Tutorials Page:
http://www.mccombs.utexas.edu/tech/training/tutorials/Excel/index.asp
5 responses so far
We want to hear from you! To keep discussions on-topic and constructive, comments are moderated for relevance and for abusive or profane language. Please note that it may take some time for your comment to appear.
1 n p // Mar 12, 2009 at 3:23 pm
c12 = tow?
2 Holly Green // Mar 13, 2009 at 12:59 pm
Oops! You caught a typo. Thanks for letting us know. We’ll fix it.
3 Jeff Hauger // Mar 13, 2009 at 1:55 pm
C12 = two
Corrected!
4 Rick // Jun 7, 2009 at 7:36 am
I have two issues with zeros in my everyday work sheets.
1- I must add a zero in fron of any number below 10. (can’t seem to get this right, tried bunches of methods)
2- leave blank spaces instead of zeros but only certain columns. Any ideas?
5 Holly Green // Jun 11, 2009 at 1:58 pm
Hi Rick,
Good questions. I had been thinking of doing a post on custom formats, and now I’m definitely planning one.
1. To show a leading zero on a single digit number you’ll use a custom format. Select the cells you want to apply the format to. Bring up the Format Cells dialog box (the keyboard shortcut Ctrl + 1 will work in 2003 or 2007). On the Number tab, click on Custom at the bottom of the Category list on the left. In the Type list on the right, select 0. Then, in the Type box above, type 00. Click OK.
2. This can also be done with a custom format. Select the column or columns you want to apply the format to. Bring up the Format Cells dialog box. Select Custom on the left, then select 0 in the list on the right. In the Type box above the list, replace the 0 with a #.
If you want to read more about custom formats, this article is very informative - http://peltiertech.com/Excel/NumberFormats.html.
Leave a Comment