SUMIF with criteria "<>" & "="


M

MikeG

I'm using excel 2007. I have 2 columns of data. A1:A8 contain various numbers only. B1:B8 contain numbers, text, & empty cells. I use the formula SUMIF(B1:B8,"<>",A1:A8). This works fine for my needs as I'm trying to sum the numbers in column A that have corresponding numbers or text in column B butnot corresponding blank cells.
If instead I replace the criteria "<>" with "=" and use the formula SUMIF(B1:B8,"=",A1:A8), I sum the numbers in column A that have correspondingblank cells. This also works with COUNTIF(A1:A8,"<>") and COUNTIF(A1:A8,"=") by either eliminating blank cells with "<>" or eliminating numbers and text with "=", thus giving the same results as COUNTA(B1:B8) or COUNTBLANK(B1:B8) respectively would.
I cannot find any built in substitutions for the =SUMIF example that I brought up first that would achieve similar results as COUNTA & COUNTBLANK do..
Why does "<>" disregard the blank cells and "=" disregard the numbers andtext?
 
Ad

Advertisements

C

Claus Busch

Hi Mike,

Am Tue, 7 May 2013 02:38:09 -0700 (PDT) schrieb MikeG:
I'm using excel 2007. I have 2 columns of data. A1:A8 contain various numbers only. B1:B8 contain numbers, text, & empty cells. I use the formula SUMIF(B1:B8,"<>",A1:A8). This works fine for my needs as I'm trying to sum the numbers in column A that have corresponding numbers or text in column B but not corresponding blank cells.
If instead I replace the criteria "<>" with "=" and use the formula SUMIF(B1:B8,"=",A1:A8), I sum the numbers in column A that have corresponding blank cells. This also works with COUNTIF(A1:A8,"<>") and COUNTIF(A1:A8,"=") by either eliminating blank cells with "<>" or eliminating numbers and text with "=", thus giving the same results as COUNTA(B1:B8) or COUNTBLANK(B1:B8) respectively would.
I cannot find any built in substitutions for the =SUMIF example that I brought up first that would achieve similar results as COUNTA & COUNTBLANK do.
Why does "<>" disregard the blank cells and "=" disregard the numbers and text?

to sum the values with corresponding values:
=SUMIF(B1:B8,"<>"&"",A1:A8)
to sum the values with correspondig blank cells:
=SUMIF(B1:B8,"="&"",A1:A8)


Regards
Claus Busch
 
C

Claus Busch

Hi Mike,

Am Tue, 7 May 2013 12:14:09 +0200 schrieb Claus Busch:
=SUMIF(B1:B8,"<>"&"",A1:A8)
=SUMIF(B1:B8,"="&"",A1:A8)

or:
=SUMIF(B1:B8,"<>",A1:A8)
=SUMIF(B1:B8,"",A1:A8)



Regards
Claus Busch
 
M

mg82152

Hi Mike,



Am Tue, 7 May 2013 12:14:09 +0200 schrieb Claus Busch:







or:

=SUMIF(B1:B8,"<>",A1:A8)

=SUMIF(B1:B8,"",A1:A8)







Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks for the reply but what I would like to know is the logic of why this works.
"<>" is equivalent to "not equal to". "=" is obvious.
If I put the letter A in cell A1, the number 2 in B1, and leave C! blank....
=A1=B1 results in FALSE. =A1=C1 results in FALSE. =B1=C1 results in FALSE.
=A1<>B1 results in TRUE. =A1<>C1 results in TRUE. =B1<>C1 results in TRUE.
All as would be logically expected.
In my original post using SUMIF(B1:B8,"<>",A1:A8), I get the results that I want BUT WHY DOES THIS WORK?
HOW DOES "<>" OR "NOT EQUAL TO" ELIMINATE BLANK CELLS?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top