sumif detects wrong rows

[Bony Pony]

Interesting one this ..

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

SUMIF #2
=sumif($a:$a,"<<Other>>BaseCost",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

 

[T. Valko]

=sumif($a:$a,"<<Other>>BaseCost",as:as)

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

greater than "<Other>>BaseCost"

And the formula blank evaluates to be greater than "<Other>>BaseCost".

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.

 

[Dave Peterson]

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.

 

[T. Valko]

Try this:

......................A...................B
1...<Other>>BaseCost.......1
2........................................1
3...=""................................1
4...<<Other>>BaseCost....1
5...<<Other>>BaseCost....1

=SUMIF(A1:A5,"<<Other>>BaseCost",B1:B5)

result = 3

A3:A5 evaluate to be greater than "<Other>>BaseCost"

 

[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="<<Other>>BaseCost"),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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Bony Pony]

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!!

 

[Dave Peterson]

Or try my followup suggestion???

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!!