Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
=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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
"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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
"Vasant Nanavati" <vasantn AT aol DOT com wrote...
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test expression for empty cell in =SUMIF()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if the range is empty | Excel Discussion (Misc queries) | |||
How to return the row # of an expression in specific array of cell | Excel Worksheet Functions | |||
Can I use more complex logical expression for sumif as creteria? | Excel Worksheet Functions | |||
Conditional Expression in cell | Excel Worksheet Functions | |||
How can I use SUMIF and AND to test for two conditons before summ. | Excel Worksheet Functions |