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.

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

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.

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

Rick

=SUMIF(range,"=",sum_range)

"fgrose" wrote:

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.

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

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

--
Biff
Microsoft Excel MVP


"fgrose" wrote in message
...
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.

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!

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

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/assistan...&respos=7&rt=2

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

"Harlan Grove" wrote in message
...
"fgrose" wrote...
...
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.

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


"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
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/assistan...&respos=7&rt=2

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

"Harlan Grove" wrote in message
...
"fgrose" wrote...
...
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.

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
__________________________________________________ _________________________

"T. Valko" wrote in message
...
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


"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
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/assistan...&respos=7&rt=2

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

"Harlan Grove" wrote in message
...
"fgrose" wrote...
...
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.

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,"")



"fgrose" wrote in message
...
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.