Sum of "everything but me".

[Maury Markowitz]

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*C24)
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

 

[Jim]

Maury,

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

First, with your data in A16 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))+SUMPRODUCT(--($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.

 

[Jim]

One moe thing...

I'm not sure how you want to count the "related to" items. Post your
expected answers and someone will be able to help you out.

Jim

 

[Billy Liddel]

Maury
I changed the headings slightly my layout is:

Parts related_to packages items/pack Stock Shortf/Over
Girders 10 10 0
Bolts Girders 2 10 20 10
Nuts Girders 1 10 10 0
Hoses 5 5 0
Nozzles Hoses 2 2 -3

Formula in E1; =IF(ISBLANK(D2),C2,C2*D2)
Formula in F1:

=SUMPRODUCT(--($A$2:$A$6=A2),($B$2:$B$6>"")*($E$2:$E$6))-SUMPRODUCT(--($A$2:$A$6=B2)*($E$2:$E$6))

Hopes this helps
Peter

 

[Maury Markowitz]

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

Yes, and thanks!

First, with your data in A16 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))+SUMPRODUCT(--($A$2:$A$6=$K2),--($D$2:$D$6=0),($C$2:$C$6))

I am going to start playing with this now! I do have two questions though.

First off, what is the "--" syntax? I have never used this before but I keep
seeing it in examples here in for the forums, so it seems like it's something
I should know about.

Secondly, what happens in the case of null columns? For this data set (which
is handed on-high from the database) the key count is total items = (quantity
times items/package). However, just due to the way the database is organized,
items that have 1 item/package are listed as having "null" items/package. Is
there a way to feed in some sort of ISNULL(items/package, 1)?

I could write all of this in a few lines of VBA, but I do that so much my
knowledge of formula crafting is seriously lacking!

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.

Ohhh, weird. Ok, one for the memory banks.

I'm not sure how you want to count the "related to" items. Post your
expected answers and someone will be able to help you out.

Oh geez, I posted inputs and no outputs! Sorry! Here's what I'm looking for

thing related to packages items/pack surplus/shortfall
Girders 10 <- nothing, this is a "root product"
Bolts Girders 1 10 -10 <- 10 girders - 10 nuts - 10 bolts
Nuts Girders 1 10 -10 <- 10 girders - 10 nuts - 10 bolts
Hoses 5 <- nothing, this is a "root product"
Nozzles Hoses 2 3 <- 5 hoses - 2 nozzles

Now all I need to do is put a conditional formatter on the surplus/shortfall
column, items smaller than zero are green and ones greater than zero are red.
Or I could invert the numbers to make suplusses positive...

Maury

 

[Maury Markowitz]

Parts related_to packages items/pack Stock Shortf/Over
Girders 10 10 0
Bolts Girders 2 10 20 10
Nuts Girders 1 10 10 0
Hoses 5 5 0
Nozzles Hoses 2 2 -3

Formula in E1; =IF(ISBLANK(D2),C2,C2*D2)
Formula in F1:

=SUMPRODUCT(--($A$2:$A$6=A2),($B$2:$B$6>"")*($E$2:$E$6))-SUMPRODUCT(--($A$2:$A$6=B2)*($E$2:$E$6))

Hopes this helps

Where should I send my first born?! Thanks!

Maury

 

[Billy Liddel]

Maury

Thanks for the feedback - you're welcome

Peter