test expression for empty cell in =SUMIF()

[fgrose]

How does one test for an empty worksheet cell in the array-type criteria for
worksheet functions like =SUMIF() ?

=SUMIF(range,"=""",sum_range) does not seem to work for me.

 

[Rick Rothstein \(MVP - VB\)]

How does one test for an empty worksheet cell in the array-type

criteria for worksheet functions like =SUMIF() ?

=SUMIF(range,"=""",sum_range) does not seem to work for me.

Do you really need SUMIF? What is wrong with using this?

=SUM(Sum_Range)

Empty cell are not a problem to it.

Rick

 

[Toppers]

=SUMIF(range,"=",sum_range)

 

[Rick Rothstein \(MVP - VB\)]

How does one test for an empty worksheet cell in the array-type

Do you really need SUMIF? What is wrong with using this?

=SUM(Sum_Range)

Empty cell are not a problem to it.

Please ignore my answer... I had someone (the wife<g>) "gently" requesting
my presence elsewhere and I rushed the answer without thinking.
..

Rick

 

[T. Valko]

This works for both empty cells and cells that contain formula blanks
(appear empty):

=SUMIF(A1:A10,"",B1:B10)

 

[fgrose]

Thank you for both working solutions.

I wish I could find documentation of this expression syntax in the Excel
Help or function reference, even as an example. Have I missed it?

If not, where would l look for this type of expression syntax for Excel?

Thanks again!

 

[Dave Thomas]

If your range is A1:A10 and the range you want to sum is B1:B10 use this:
=SUMIF(A1:A10,"",B1:B10), this will return the sum of the numbers in B1 thru
B10 that have corresponding empty cells in A1 thru A10. If you simply want
to count the empty cells in A1 thru A10, use: =COUNTIF(A1:A10,"")

 

[Harlan Grove]

I wish I could find documentation of this expression syntax in the Excel
Help or function reference, even as an example. Have I missed it?

If not, where would l look for this type of expression syntax for Excel?

Unfortunately Microsoft doesn't seem to be interested in providing complete
documentation of Excel functions. These newsgroups are the closest thing
you'll find to providing such documentation, but they're somewhat
disorganized. Even so, searching the Google Groups archive is the best way
to find the most detailed explanation of how Excel's functions work.

 

[Vasant Nanavati]

I hate to disagree with you, Harlan. But while this is not perfect, it's a
fairly decent worksheet function reference:

http://office.microsoft.com/assista...C010229861033&QueryID=D0mB0qSjX&respos=7&rt=2

Unfortunately when you search Help for "Worksheet Functions," it's the 7th
item displayed.
__________________________________________________________________________

 

[T. Valko]

I think Harlan's point is this:

[not] providing complete documentation of Excel functions.

And I agree.

Here's a perfect example.

I replied to a post just a few posts above this one about the number of days
in a month.

From the link your provided on the MONTH function:
**********
MONTH(serial_number)

Serial_number is the date of the month you are trying to find. Dates
should be entered by using the DATE function, or as results of other
formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of
May, 2008. Problems can occur if dates are entered as text.

**********

A1 = June

=MONTH(A1&1) = 6

A1 is clearly not a date or date_serial_number. Using the & concatenation
operator further forces the string argument as TEXT. Yet the formula returns
the correct result. No mention of this behavior whatsoever in the MONTH
function documentation. In fact, it warns you about entering TEXT, but:

=MONTH("6/1/2007") = 6

I think that's what Harlan's pointing out.

 

[Vasant Nanavati]

Biff, I agree that it's woefully inadequate for expert users. But ti's still
not a bad reference for the average user. As to your example, Excel is
coercing the text to a date, but it's probably not the preferred way to
present the argument. You could make the same complaint about VBA, where a 0
is coerced to a FALSE and vice versa, depending on the context.

As far as trying to guess what Harlan is pointing out is concerned, he's
usually so far ahead of me that I can't presume to know. <g>
___________________________________________________________________________

I think Harlan's point is this:

[not] providing complete documentation of Excel functions.

And I agree.

Here's a perfect example.

I replied to a post just a few posts above this one about the number of
days in a month.

From the link your provided on the MONTH function:
**********
MONTH(serial_number)

Serial_number is the date of the month you are trying to find. Dates
should be entered by using the DATE function, or as results of other
formulas or functions. For example, use DATE(2008,5,23) for the 23rd day
of May, 2008. Problems can occur if dates are entered as text.

**********

A1 = June

=MONTH(A1&1) = 6

A1 is clearly not a date or date_serial_number. Using the & concatenation
operator further forces the string argument as TEXT. Yet the formula
returns the correct result. No mention of this behavior whatsoever in the
MONTH function documentation. In fact, it warns you about entering TEXT,
but:

=MONTH("6/1/2007") = 6

I think that's what Harlan's pointing out.

--
Biff
Microsoft Excel MVP

I hate to disagree with you, Harlan. But while this is not perfect, it's a
fairly decent worksheet function reference:

http://office.microsoft.com/assista...C010229861033&QueryID=D0mB0qSjX&respos=7&rt=2

Unfortunately when you search Help for "Worksheet Functions," it's the
7th item displayed.
__________________________________________________________________________

 

[Harlan Grove]

Biff, I agree that it's woefully inadequate for expert users. But ti's
still not a bad reference for the average user. As to your example, Excel
is coercing the text to a date, but it's probably not the preferred way to
present the argument. You could make the same complaint about VBA, where a
0 is coerced to a FALSE and vice versa, depending on the context.

....

Note my use of the adjective COMPLETE. Note also the OP's follow-up asking
where to find documentation of [criteria] expression syntax for SUMIF [and
COUNTIF]. Take a look at the PATHETIC entries for SUMIF and COUNTIF in the
document to which you provided a url. Any mention of the difference between
criteria "" and "="? Aside from the few examples, any mention of comparison
operators? Any mention of whether comparison operators could be used with
wildcards? Any mention of criteria being specific to text or numbers?

No to all of those questions.

I mean documentation for expert users or those who'd like to try to become
expert users. To me that includes people asking in newsgroups where to find
more documentation for Excel functions. Microsoft doesn't provide such
documentation.

 

[fgrose]

Thank you Harlan and others.

It seems that each function is free to handle data in its own peculiar way
if the developers thought it might be useful.

Since Excel is used so extensively, it seems there is a great opportunity
for the expert community to create a wiki or similar website to collect and
document all the known peculiarities for the Excel worksheet functions.

In my searches I've come across some good references, but they seemed to be
mostly individual efforts.

I'm imagining a wiki with pages for each worksheet function where MVPs and
experts could address the known function peculiarities and opportunities and
users could post discussion questions to help the experts refine the
explanations.

Perhaps such a site exists, or perhaps the wiki style of collaboration has
raised expectations.