Why inconsistent results from "SUMIF"?

[Tylers]

I'm using sumif to do a simple total from a "wholesale" column based on
matching a name in the "Vendor" column. I was using labels but have noticed
inconsistent results. Just for kicks I setup four of the same formulas with
the exception of the use of labels.

They were as such:
=SUMIF(Vendor,"Color Spot",Wholesale)
=SUMIF(Vendor,"Color Spot",F:F)
=SUMIF(J:J,"Color Spot",Wholesale)
=SUMIF(J:J,"Color Spot",F:F)

The only one I get the correct answer ($5.19) from is the last example where
I use strictly column references. The middle two examples return $0.00 and
the first one gives me $6.04 (huh?!?) Anytime a label is used in place of
an absolute column reference the results vary.

Any suggestions as to why? Thanks...

 

[Frank Kabel]

Hi
could you post your exact name definitions (from the dialog 'Insert -
Name - Define') for the two defined names

 

[Tylers]

Interesting you point that out. I looked them and saw some discrepencies in
cell ranges, although the range indicated would have captured the cells in
question. (I sorted by Vendor and the one I was trying to match was in the
first few rows). At any rate I updated the names to include the full range
for the associated column and now I have better results. Formulas 1,2 and 4
now all show the correct result. Formula 3 still shows $0.00.

Question on the name definitions: Are these what I use within the formulas
to reference that column? I have columns with multiple words (eg. "Sale
Item") and I see where Excel replaces whitespace with underscores. Which do
I use?

Also, I have multiple worksheets with the same set of labels, yet it appears
the name definitions are workbook-wide? I see that some of the names
reference different worksheets. I'm not quite clear yet on the concept of
names vs. labels. Any insight would be appreciated.

 

[Dave Peterson]

First, I don't recall ever seeing anyone recommending using those natural
language labels.

I know I don't use them and turned that option off.
(Tools|options|calculation tab|uncheck "accept labels in formulas")

But names are very useful.

I like to select my range and then either Insert|Name|Define
or just type the name into the namebox (to the left of the formula bar)
(remember to hit enter)

Then you can use those range names in your formulas.

Do this against a test workbook--just in case.
Select all of column J and assign it Vendor (after turning off that option).

Then check out your formulas.

And if you're going to continue using names, get a copy of the newly released
3.2 version of Jan Karel Pieterse's
(with Charles Williams and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

 

[Tylers]

Thanks. I'll try that.

The other question is what's the best way to use sumif to total a series of
rows NOT containing a list of names. I am using it now to total up products
from certain vendors. I only want to single out a few vendors and wish to
lump the rest as "others'. So I basically need to do a sumif with a series
of "nots" to get the miscellaneous vendors.

 

[Dave Peterson]

I think I'd set up a column on another worksheet with all the names to exclude.
(I used sheet2!a1:a10 as the range that held that list).

Then I used a formula like:

=SUMPRODUCT(--ISERROR(MATCH(A1:A10,sheet2!A1:A10,0)),B1:B10)

To add up B1:B10, but only if A1:A10 was not on that list.

=====
Another alternative.

Put an extra column with a header of KeepIt in your data and use:

=match(a2,sheet2!a:a,0)
and drag down your range.

Then use Data|pivottable to create a nice summary table.

But drag that keepit field to the page header. You can "filter" to show just
the ones to keep, the ones you don't care about or both.

Thanks. I'll try that.

The other question is what's the best way to use sumif to total a series of
rows NOT containing a list of names. I am using it now to total up products
from certain vendors. I only want to single out a few vendors and wish to
lump the rest as "others'. So I basically need to do a sumif with a series
of "nots" to get the miscellaneous vendors.

 

[Dave Peterson]

And depending on what's on sheet2, that keepit formula should be:

=iserror(match(a2,sheet2!a:a,0))

(since you put the "bad" ones there, you want to check to see that it's not a
match.)

Thanks. I'll try that.

The other question is what's the best way to use sumif to total a series of
rows NOT containing a list of names. I am using it now to total up products
from certain vendors. I only want to single out a few vendors and wish to
lump the rest as "others'. So I basically need to do a sumif with a series
of "nots" to get the miscellaneous vendors.

 

[Dave Peterson]

Doh!

This formula (for the pivottable stuff), should be:

=match(a2,sheet2!a:a,0)

=iserror(match(...))

or
=isnumber(match(...))

I wanted to return true/false to each cell in that column.

(But I did get it in the first revised post!)

I think I'd set up a column on another worksheet with all the names to exclude.
(I used sheet2!a1:a10 as the range that held that list).

Then I used a formula like:

=SUMPRODUCT(--ISERROR(MATCH(A1:A10,sheet2!A1:A10,0)),B1:B10)

To add up B1:B10, but only if A1:A10 was not on that list.

=====
Another alternative.

Put an extra column with a header of KeepIt in your data and use:

=match(a2,sheet2!a:a,0)
and drag down your range.

Then use Data|pivottable to create a nice summary table.

But drag that keepit field to the page header. You can "filter" to show just
the ones to keep, the ones you don't care about or both.

 

[Aladin Akyurek]

Thanks. I'll try that.

The other question is what's the best way to use sumif to total
series of
rows NOT containing a list of names. I am using it now to total u
products
from certain vendors. I only want to single out a few vendors and wis
to
lump the rest as "others'. So I basically need to do a sumif with
series
of "nots" to get the miscellaneous vendors.

[...]

To lump the rest as "others"...

=SUM(RangeToSum)-SUMIF(VendorRange,Vendor,RangeToSum)

=SUM(RangeToSum)-SUMPRODUCT(--ISNUMBER(MATCH(VendorRange,VendorList,0)),RangeToSum