Maury,

Lots of questions there - but I'll take a stab at some of them.

First, with your data in A1:D6 and a list of your unique things in K2:K6,
the following formula in L2 (and copied to L3:L6) will give you the correct
count of the unique things:

=SUMPRODUCT(--($A$2:$A$6=$K2),($C$2:$C$6),($D$2:$D$6))+SUMPRODUC T(--($A$2:$A$6=$K2),--($D$2:$D$6=0),($C$2:$C$6))

Second, your SUM formula is an array function and must be committed
(entered) with Ctrl-Shift-Enter to produce {sum(____:_____)}. if you just
hit enter, you will not have an array function.

"Maury Markowitz" wrote:

I have a sheet of inventory broken down by location. Some items are related
to other items, for instance, there are nuts and bolts that are needed to
fasten girders. We maintain this relationship through the primary IDs in our
associated database; if an item in question has a "underid", then it is
related to some other item. For instance:

thing related to packages items/pack
Girders 10
Bolts Girders 1 10
Nuts Girders 1 10
Hoses 5
Nozzles Hoses 2

The formula I'm trying to write is to make sure that I don't have a
shortfall of any one item at any one location. In the example above, we have
enough nuts and bolts for the girders, but we don't have enough nozzles for
the hoses. So what I need to know is whether or not the quantity of any given
item is enough to "cover" any other related item.

Mathemetically it's (total quant of all related items)-(total quant of this
item) If that number is bigger than zero, we're cool.

Now this looks like something you could do with a SUMPRODUCT, right? Well no
such luck (I think). As you can see, the database is lazy and doesn't put in
default values for items per pack if it's only 1. So I need to put an
ISNULL(quantity,1) in there. Is there a way to do this in SUMPRODUCT?

Then I looked at using just SUM for this, but I can't figure out how this
works. In the SUMPRODUCT help there is a very confusing statement; "The
preceding example returns the same result as the formula SUM(A2:B4*C2:D4)
entered as an array" So I tried a few experiments with SUM, but I can't
figure out what it's doing. I put two columns of four numbers in a sheet and
used =SUM(A1:A4*B1:B4), and the result was simply the value of A*B, it didn't
sum up over the rows. Am I missing something obvious here?

The other problem is the one that really has me stumped. The grouping for
summing up is basically if my underid is in either the first OR second
column. That is, the total is anything that has my underid as its underid, or
anything that has my underid as its ID. The good news is that it's basically
an ISNULL(underid, ID), because we want to use ID only if

Anyone have some suggestions? BTW, the sheet is 1500 rows long, and about 50
columns wide, so major surgery in terms of adding new related columns might
not be easy.

Maury