Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |