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($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

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

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

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

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

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