sumif detects wrong rows
 

Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with the
formula irrespective of the result! Any ideas?

Best regards!
Robert

sumif detects wrong rows
 

=sumif($a:$a,"<<OtherBaseCost",as:as)

What's happening is the criteria is being evaluated as:

greater than "<OtherBaseCost"

And the formula blank evaluates to be greater than "<OtherBaseCost".

Little known about SUMIF and COUNTIF is that they can determine < and on
TEXT.

Consider this:

x...5
y...2

=SUMIF(A1:A2."x",B1:B2)

Result = 2

x...5
a...2

=SUMIF(A1:A2."x",B1:B2)

Result = 0

So, you should probably use SUMPRODUCT.


--
Biff
Microsoft Excel MVP


"Bony Pony" <Bony wrote in message
...
Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with
the
formula irrespective of the result! Any ideas?

Best regards!
Robert

sumif detects wrong rows
 

I couldn't get it to break in my tests.

I'd suggest that you create a new test worksheet and try it with a smaller
amount of data.



Bony Pony wrote:

Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with the
formula irrespective of the result! Any ideas?

Best regards!
Robert

--

Dave Peterson

sumif detects wrong rows
 

Try this:

......................A...................B
1...<OtherBaseCost.......1
2........................................1
3...=""................................1
4...<<OtherBaseCost....1
5...<<OtherBaseCost....1

=SUMIF(A1:A5,"<<OtherBaseCost",B1:B5)

result = 3

A3:A5 evaluate to be greater than "<OtherBaseCost"


--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
I couldn't get it to break in my tests.

I'd suggest that you create a new test worksheet and try it with a smaller
amount of data.



Bony Pony wrote:

Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are
naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with
the
formula irrespective of the result! Any ideas?

Best regards!
Robert

--

Dave Peterson

sumif detects wrong rows
 

After I read your other response, I realized that my test data wasn't very good.

And to add to your other post about =sumproduct():

=sumproduct(--(a1:a5="<<OtherBaseCost"),B1:B5)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

"T. Valko" wrote:

Try this:

.....................A...................B
1...<OtherBaseCost.......1
2........................................1
3...=""................................1
4...<<OtherBaseCost....1
5...<<OtherBaseCost....1

=SUMIF(A1:A5,"<<OtherBaseCost",B1:B5)

result = 3

A3:A5 evaluate to be greater than "<OtherBaseCost"

--
Biff
Microsoft Excel MVP

"Dave Peterson" wrote in message
...
I couldn't get it to break in my tests.

I'd suggest that you create a new test worksheet and try it with a smaller
amount of data.



Bony Pony wrote:

Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are
naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with
the
formula irrespective of the result! Any ideas?

Best regards!
Robert

--

Dave Peterson

--

Dave Peterson

sumif detects wrong rows
 

Hi Dave and Biff,
Many thanks for taking the time to look at this. I suspected that SUMIF was
interpreting the < as a mathematical function. I got a workaround by having
the original formulas that were resolving to "" to resolve to a "n" instead.
Clumsy but effective.

Will work in the Sumproduct solution when I have the time.

Thanks again!!

"Bony Pony" wrote:

Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with the
formula irrespective of the result! Any ideas?

Best regards!
Robert

sumif detects wrong rows
 

Or try my followup suggestion???

Bony Pony wrote:

Hi Dave and Biff,
Many thanks for taking the time to look at this. I suspected that SUMIF was
interpreting the < as a mathematical function. I got a workaround by having
the original formulas that were resolving to "" to resolve to a "n" instead.
Clumsy but effective.

Will work in the Sumproduct solution when I have the time.

Thanks again!!

"Bony Pony" wrote:

Interesting one this ..

SUMIF #1
=sumif($a:$a,"OtherBaseCost",as:as)
works perfectly.

SUMIF #2
=sumif($a:$a,"<<OtherBaseCost",as:as)
doesn't.

In Col A at which the sumif is looking, there are cells that are naturally
empty and cells that contain a formula similar to:
=if(a5="BaseCost",a5,"")
SUMIF #2 rightly ignores the naturally empty ones but sums the row with the
formula irrespective of the result! Any ideas?

Best regards!
Robert


--

Dave Peterson