SUMIF non-blank cells?

[Blue Max]

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]

You want to compare column A to non-blanks, right???

=SUMIF(A$8:A20,"<>",B$8:B20)

 

[Blue Max]

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]

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]

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]

I think you've got it <vbg>.

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

**********************