Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 113
Default SUMIF non-blank 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
NON-BLANK cells in the sum_range column. How do we create a formula that
will sum the non-blank 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 non-blank 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 work-around. We thank you for any help with this
issue.

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: SUMIF non-blank cells?

Yes, there is a criteria that will identify the non-blank cells in the sum_range column. You can use the
Formula:
"<" 
operator in the criteria argument of the
Formula:
SUMIF 
function to sum the non-blank cells. Here's how you can modify the formula:
  1. Formula:
    Formula:
    =SUMIF(A$8:A20,"<",B$8:B20
  2. This formula will sum all the cells in the
    Formula:
    B$8:B20 
    range that have a corresponding non-blank cell in the
    Formula:
    A$8:A20 
    range.
  3. The
    Formula:
    "<" 
    operator means "not equal to". So, the criteria argument
    Formula:
    "<" 
    will match all cells that are not blank.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default SUMIF non-blank cells?

You want to compare column A to non-blanks, 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
NON-BLANK cells in the sum_range column. How do we create a formula that
will sum the non-blank 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 non-blank 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 work-around. We thank you for any help with this
issue.


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 113
Default SUMIF non-blank 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 non-zero, non-text, 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 non-blanks, 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
NON-BLANK cells in the sum_range column. How do we create a formula that
will sum the non-blank 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 non-blank 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 work-around. We thank you for any help with
this
issue.


--

Dave Peterson


  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default SUMIF non-blank 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 non-zero, non-text, 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 non-blanks, 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
NON-BLANK cells in the sum_range column. How do we create a formula that
will sum the non-blank 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 non-blank 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 work-around. We thank you for any help with
this
issue.


--

Dave Peterson


--

Dave Peterson


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 113
Default SUMIF non-blank 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 non-zero, non-text, 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 non-blanks, 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
NON-BLANK cells in the sum_range column. How do we create a formula
that
will sum the non-blank 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 non-blank 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 work-around. We thank you for any help
with
this
issue.

--

Dave Peterson


--

Dave Peterson


  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default SUMIF non-blank 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 non-zero, non-text, 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 non-blanks, 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
NON-BLANK cells in the sum_range column. How do we create a formula
that
will sum the non-blank 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 non-blank 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 work-around. We thank you for any help
with
this
issue.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumif returns blank if all cells blank Heliocracy Excel Worksheet Functions 7 January 19th 09 09:59 PM
SUMIF except for blank cells Hagridore Excel Discussion (Misc queries) 3 November 26th 08 10:16 AM
SUMIF does not work with blank cells uncreative Excel Discussion (Misc queries) 3 August 23rd 07 07:36 PM
Sumif Cells Are Not Blank Powlaz Excel Worksheet Functions 12 March 15th 06 05:40 PM
Using SUMIF with non-blank cells Peter Aitken Excel Worksheet Functions 7 February 10th 05 03:11 PM


All times are GMT +1. The time now is 03:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"