Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.newusers




SUMIF nonblank cells?
We have been able to conditionally sum a column of cells (sum_range) based
on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NONBLANK cells in the sum_range column. How do we create a formula that will sum the nonblank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the nonblank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome workaround. We thank you for any help with this issue. 
#2




Answer: SUMIF nonblank cells?
Yes, there is a criteria that will identify the nonblank cells in the sum_range column. You can use the
Formula:
Formula:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.newusers




SUMIF nonblank cells?
You want to compare column A to nonblanks, right???
=SUMIF(A$8:A20,"<",B$8:B20) Blue Max wrote: We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NONBLANK cells in the sum_range column. How do we create a formula that will sum the nonblank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the nonblank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome workaround. We thank you for any help with this issue.  Dave Peterson 
#4
Posted to microsoft.public.excel.newusers




SUMIF nonblank cells?
Thanks, Dave, I can't believe I overlooked such a simple solution! Your
suggestion worked perfectly. However, I still would like to know why the NOT() and ISBANK() functions did not work. I also would like to know exactly why the "<" format does work? When a value is not specified, is this criteria comparing whether the value is nonzero, nontext, or blank by default? I would like to understand better how this criteria works so that I could use it under a variety of circumstances. Thank you so much for a great solution, Richard ************ "Dave Peterson" wrote in message ... You want to compare column A to nonblanks, right??? =SUMIF(A$8:A20,"<",B$8:B20) Blue Max wrote: We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NONBLANK cells in the sum_range column. How do we create a formula that will sum the nonblank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the nonblank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome workaround. We thank you for any help with this issue.  Dave Peterson 
#5
Posted to microsoft.public.excel.newusers




SUMIF nonblank cells?
If you wanted to check to see if the range was equal to the string "asdf", you
could use: =sumif(a1:a10,"asdf",b1:b10) or =sumif(a1:a10,"=asdf",b1:b10) or even =sumif(a1:a10,"="&"asdf",b1:b10) if you wanted to check the cells were not equal to "asdf", you could use: =sumif(a1:a10,"<asdf",b1:b10) or even =sumif(a1:a10,"<"&"asdf",b1:b10) Replacing that "asdf" with an empty string: "<"&"" or which evaluates to simply: "<" Blue Max wrote: Thanks, Dave, I can't believe I overlooked such a simple solution! Your suggestion worked perfectly. However, I still would like to know why the NOT() and ISBANK() functions did not work. I also would like to know exactly why the "<" format does work? When a value is not specified, is this criteria comparing whether the value is nonzero, nontext, or blank by default? I would like to understand better how this criteria works so that I could use it under a variety of circumstances. Thank you so much for a great solution, Richard ************ "Dave Peterson" wrote in message ... You want to compare column A to nonblanks, right??? =SUMIF(A$8:A20,"<",B$8:B20) Blue Max wrote: We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NONBLANK cells in the sum_range column. How do we create a formula that will sum the nonblank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the nonblank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome workaround. We thank you for any help with this issue.  Dave Peterson  Dave Peterson 
#6
Posted to microsoft.public.excel.newusers




SUMIF nonblank cells?
Thank your for the additional information, Dave. If I understand correctly
then, the "<" logical operator literally represents 'Not Equal', but also infers the values are being compared to a null string when a specific value is not specified (hence "<" is the same as "<"&""). With that, I think I understand. Thanks again, Richard ********************** "Dave Peterson" wrote in message ... If you wanted to check to see if the range was equal to the string "asdf", you could use: =sumif(a1:a10,"asdf",b1:b10) or =sumif(a1:a10,"=asdf",b1:b10) or even =sumif(a1:a10,"="&"asdf",b1:b10) if you wanted to check the cells were not equal to "asdf", you could use: =sumif(a1:a10,"<asdf",b1:b10) or even =sumif(a1:a10,"<"&"asdf",b1:b10) Replacing that "asdf" with an empty string: "<"&"" or which evaluates to simply: "<" Blue Max wrote: Thanks, Dave, I can't believe I overlooked such a simple solution! Your suggestion worked perfectly. However, I still would like to know why the NOT() and ISBANK() functions did not work. I also would like to know exactly why the "<" format does work? When a value is not specified, is this criteria comparing whether the value is nonzero, nontext, or blank by default? I would like to understand better how this criteria works so that I could use it under a variety of circumstances. Thank you so much for a great solution, Richard ************ "Dave Peterson" wrote in message ... You want to compare column A to nonblanks, right??? =SUMIF(A$8:A20,"<",B$8:B20) Blue Max wrote: We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NONBLANK cells in the sum_range column. How do we create a formula that will sum the nonblank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the nonblank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome workaround. We thank you for any help with this issue.  Dave Peterson  Dave Peterson 
#7
Posted to microsoft.public.excel.newusers




SUMIF nonblank cells?
I think you've got it <vbg.
Blue Max wrote: Thank your for the additional information, Dave. If I understand correctly then, the "<" logical operator literally represents 'Not Equal', but also infers the values are being compared to a null string when a specific value is not specified (hence "<" is the same as "<"&""). With that, I think I understand. Thanks again, Richard ********************** "Dave Peterson" wrote in message ... If you wanted to check to see if the range was equal to the string "asdf", you could use: =sumif(a1:a10,"asdf",b1:b10) or =sumif(a1:a10,"=asdf",b1:b10) or even =sumif(a1:a10,"="&"asdf",b1:b10) if you wanted to check the cells were not equal to "asdf", you could use: =sumif(a1:a10,"<asdf",b1:b10) or even =sumif(a1:a10,"<"&"asdf",b1:b10) Replacing that "asdf" with an empty string: "<"&"" or which evaluates to simply: "<" Blue Max wrote: Thanks, Dave, I can't believe I overlooked such a simple solution! Your suggestion worked perfectly. However, I still would like to know why the NOT() and ISBANK() functions did not work. I also would like to know exactly why the "<" format does work? When a value is not specified, is this criteria comparing whether the value is nonzero, nontext, or blank by default? I would like to understand better how this criteria works so that I could use it under a variety of circumstances. Thank you so much for a great solution, Richard ************ "Dave Peterson" wrote in message ... You want to compare column A to nonblanks, right??? =SUMIF(A$8:A20,"<",B$8:B20) Blue Max wrote: We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NONBLANK cells in the sum_range column. How do we create a formula that will sum the nonblank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the nonblank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome workaround. We thank you for any help with this issue.  Dave Peterson  Dave Peterson  Dave Peterson 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
sumif returns blank if all cells blank  Excel Worksheet Functions  
SUMIF except for blank cells  Excel Discussion (Misc queries)  
SUMIF does not work with blank cells  Excel Discussion (Misc queries)  
Sumif Cells Are Not Blank  Excel Worksheet Functions  
Using SUMIF with nonblank cells  Excel Worksheet Functions 