SUMIF Criterion with NOT
 

Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

Use the math operators <

=SUMIF(A3:A1000,"<Total*",C3:C1000)

Mike

"Fred Holmes" wrote:

Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

hi,
try this...
=SUMIF(A3:A1000,"<Total*",C3:C1000)

< = not

Regards
FSt1

"Fred Holmes" wrote:

Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

Thanks, I'll give it a shot. I would never have expected the math
operators to work inside the quotation marks, i.e., I would expect
that they would be interpreted as characters in the srting to be
matched.

On Fri, 8 Jun 2007 00:48:02 -0700, Mike H
wrote:

Use the math operators <

=SUMIF(A3:A1000,"<Total*",C3:C1000)

Mike

"Fred Holmes" wrote:

Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

It's odd isn't it. It must be because SUMIF doesn't use the operator in its
definition, = is assumed, so if you don't want = you have to be explicit,
and there is nowhere else to put it. I think it probably didn't support <,
and that was how they squeezed it in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Fred Holmes" wrote in message
...
Thanks, I'll give it a shot. I would never have expected the math
operators to work inside the quotation marks, i.e., I would expect
that they would be interpreted as characters in the srting to be
matched.

On Fri, 8 Jun 2007 00:48:02 -0700, Mike H
wrote:

Use the math operators <

=SUMIF(A3:A1000,"<Total*",C3:C1000)

Mike

"Fred Holmes" wrote:

Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

"<"& "total"
--
Don Guillett
SalesAid Software

"Fred Holmes" wrote in message
...
Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

Actually, the suggested "<Total*" appears to work just fine.

It is logically equivalent to your suggested "<"&"Total*".

Originally, I didn't think of using wild cards in the string, but that
does seem to make sense.

Fred Holmes

On Fri, 8 Jun 2007 08:08:50 -0500, "Don Guillett"
wrote:


"<"& "total"
--
Don Guillett
SalesAid Software

"Fred Holmes" wrote in message
.. .
Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes

SUMIF Criterion with NOT
 

6 of one = 1/2 dozen of another.

--
Don Guillett
SalesAid Software

"Fred Holmes" wrote in message
...
Actually, the suggested "<Total*" appears to work just fine.

It is logically equivalent to your suggested "<"&"Total*".

Originally, I didn't think of using wild cards in the string, but that
does seem to make sense.

Fred Holmes

On Fri, 8 Jun 2007 08:08:50 -0500, "Don Guillett"
wrote:


"<"& "total"
--
Don Guillett
SalesAid Software

"Fred Holmes" wrote in message
. ..
Excel 2000

Is there a way to write a SUMIF formula such that one sums rows that
do NOT match the criterion? Something like:

=SUMIF(A3:A1000,NOT("Total*"),C3:C1000)

But the middle item, the criterion, needs to be a string, not a
logical.

Many thanks,

Fred Holmes