SUMIF with criteria "<>" & "="

  • Thread starter Thread starter MikeG
  • Start date Start date
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?
 
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?
Click to expand...

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
 
Hi Mike,

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

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



Regards
Claus Busch
 
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
Click to expand...

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?
 
Back
Top