how to write 'not equals blank' criteria in SUMIF

Hi, and TIA for your time.
I've tried SUMIF(A1:C1,"<>"""",A2:C2) where the 2nd argument needs to be
'not equal to blank. Please advise on the correct syntax.
Thanks
--
David

try
=SUMIF(A1:C1,"<>"&"",A2:C2)
"David" wrote:

> Hi, and TIA for your time.
> I've tried SUMIF(A1:C1,"<>"""",A2:C2) where the 2nd argument needs to be
> 'not equal to blank. Please advise on the correct syntax.
> Thanks
> --
> David

Enter the criteria <>"" in a cell then reference the cell instead of typing
the criteria directly in the formula

B1 contains <>""

=SUMIF(A1:C1,B1,A2:C2)

Nick


"David" <[email protected]> wrote in message
news:[email protected]...
> Hi, and TIA for your time.
> I've tried SUMIF(A1:C1,"<>"""",A2:C2) where the 2nd argument needs to be
> 'not equal to blank. Please advise on the correct syntax.
> Thanks
> --
> David

Nick wrote...
>Enter the criteria <>"" in a cell then reference the cell instead of
typing
>the criteria directly in the formula
>
>B1 contains <>""
>
>=SUMIF(A1:C1,B1,A2:C2)

First off, B1 would be contained in A1:C1, so bad choice of cell to
contain the criteria. Then, that criteria doesn't do what you think it
does - it'll exclude cells evaluating to the string containing two
double quotes. To exclude blank cells, use

=SUMIF(A1:C1,"<>",A2:C2)

Note that this won't exclude columns in which the row 1 cell evaluates
to a zero length string, "". To exclude those as well, easier to use

=SUMPRODUCT(--(A1:C1<>""),A2:C2)

Thanks again Harlan
--
David

OK good spot, the formula should have read =SUMIF(A1:A3,C1,B1:B3)
with the entry <>"" in cell C1.

You are then summing values in B1:B3 where values in A1:A3 are not blank ie
they contain something even if it is a space.

That works.

Nick



"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Nick wrote...
>>Enter the criteria <>"" in a cell then reference the cell instead of
> typing
>>the criteria directly in the formula
>>
>>B1 contains <>""
>>
>>=SUMIF(A1:C1,B1,A2:C2)
>
> First off, B1 would be contained in A1:C1, so bad choice of cell to
> contain the criteria. Then, that criteria doesn't do what you think it
> does - it'll exclude cells evaluating to the string containing two
> double quotes. To exclude blank cells, use
>
> =SUMIF(A1:C1,"<>",A2:C2)
>
> Note that this won't exclude columns in which the row 1 cell evaluates
> to a zero length string, "". To exclude those as well, easier to use
>
> =SUMPRODUCT(--(A1:C1<>""),A2:C2)
>

"Nick" <[email protected]> wrote...
>OK good spot, the formula should have read =SUMIF(A1:A3,C1,B1:B3)
>with the entry <>"" in cell C1.
>
>You are then summing values in B1:B3 where values in A1:A3 are not blank ie
>they contain something even if it is a space.
>
>That works.
....

That does not work, and you seem not to have tested it last time or this
time.

In a new worksheet, leave A1 blank, enter ="" in A2 and ="""""" in A3, and
enter {1;10;100} in B1:B3. Enter <>"" in C1 and your formula in D1. What
does it return? On my system it returns 11, the sum of B1 and B2, which
correspond to the cells A1 and A2, which appear blank/empty.

Change C1 to <> and D1 returns 110, which reflects the nasty truth that
SUMIF doesn't handle zero length strings as most people expect it should.