dan9591
asked on
Tough excel math formula
This should be simple, but it’s driving me nuts…
I have a spreadsheet that looks like this:
Part Number Lot Number Quantity
020-044 Goods 8
020-044 CAH217 -5
020-044 VEG076 1
020-044 Total 4
020-045 Goods 6
020-045 CNS015 -4
020-045 SPG015 -1
020-045 CAH217 -1
020-045 VEG076 1
020-045 Total 1
020-056 Goods 16
020-056 FLX017 -4
020-056 CAS037 -6
020-056 LAS006 -3
020-056 FLP476 -1
020-056 Total 2
What I need is a formula that will go through the spread sheet and copy the total of each part number to the cel next to the “goods” line for each entry. For instance, part #020-044 has a total of 4 units. I simply need that “4” copied to cel D2. The end result should look like this:
Part Number Lot Number Quantity Total
020-044 Goods 8 4
020-044 CAH217 -5
020-044 VEG076 1
020-044 Total 4
020-045 Goods 6 1
020-045 CNS015 -4
020-045 SPG015 -1
020-045 CAH217 -1
020-045 VEG076 1
020-045 Total 1 2
020-056 Goods 16
020-056 FLX017 -4
020-056 CAS037 -6
020-056 LAS006 -3
020-056 FLP476 -1
020-056 Total 2
There are over 50,000 part numbers on this spreadsheet. Some have one or two lot numbers; some have over a dozen, so doing this manually is not an option. I am using MS Excel 2010.
I have a spreadsheet that looks like this:
Part Number Lot Number Quantity
020-044 Goods 8
020-044 CAH217 -5
020-044 VEG076 1
020-044 Total 4
020-045 Goods 6
020-045 CNS015 -4
020-045 SPG015 -1
020-045 CAH217 -1
020-045 VEG076 1
020-045 Total 1
020-056 Goods 16
020-056 FLX017 -4
020-056 CAS037 -6
020-056 LAS006 -3
020-056 FLP476 -1
020-056 Total 2
What I need is a formula that will go through the spread sheet and copy the total of each part number to the cel next to the “goods” line for each entry. For instance, part #020-044 has a total of 4 units. I simply need that “4” copied to cel D2. The end result should look like this:
Part Number Lot Number Quantity Total
020-044 Goods 8 4
020-044 CAH217 -5
020-044 VEG076 1
020-044 Total 4
020-045 Goods 6 1
020-045 CNS015 -4
020-045 SPG015 -1
020-045 CAH217 -1
020-045 VEG076 1
020-045 Total 1 2
020-056 Goods 16
020-056 FLX017 -4
020-056 CAS037 -6
020-056 LAS006 -3
020-056 FLP476 -1
020-056 Total 2
There are over 50,000 part numbers on this spreadsheet. Some have one or two lot numbers; some have over a dozen, so doing this manually is not an option. I am using MS Excel 2010.
Here, you can do this with a simple SUMIFS formula: Put this in column D, on the Goods line and see:
=SUMIFS($C$2:$C$17,$A$2:$A $17,$A2,$B $2:$B$17," <>Total")
See attached.
Dave
Goods-Total-r1.xlsx
=SUMIFS($C$2:$C$17,$A$2:$A
See attached.
Dave
Goods-Total-r1.xlsx
This is a bit better - so paste the formula and copy down...
=IF(B2="Goods",SUMIFS($C$2 :$C$17,$A$ 2:$A$17,$A 2,$B$2:$B$ 17,"<>Tota l"),"")
see attached,
Dave
Goods-Total-r2.xlsx
=IF(B2="Goods",SUMIFS($C$2
see attached,
Dave
Goods-Total-r2.xlsx
Not for points.
Since I had already worked on it. Here is an alternative.
This goes in cell D2.
=SUMPRODUCT(($A$2:$A$17=A2 )*($B$2:$B $17="Total ")*(C2:C17 ))
This in D6
=SUMPRODUCT(($A$2:$A$17=A6 )*($B$2:$B $17="Total ")*($C$2:$ C$17))
and this in D12
=SUMPRODUCT(($A$2:$A$17=A1 2)*($B$2:$ B$17="Tota l")*($C$2: $C$17))
Sid
Since I had already worked on it. Here is an alternative.
This goes in cell D2.
=SUMPRODUCT(($A$2:$A$17=A2
This in D6
=SUMPRODUCT(($A$2:$A$17=A6
and this in D12
=SUMPRODUCT(($A$2:$A$17=A1
Sid
ASKER
Sorry, maybe I wasn’t too clear…
1. I do not want to re-calculate anything. The totals are already calculated. I simply need to copy the total from one cel to another.
2. There are thousands of part numbers, each with up to 20 lot numbers. It is not feasible to paste a formula for each part number manually.
1. I do not want to re-calculate anything. The totals are already calculated. I simply need to copy the total from one cel to another.
2. There are thousands of part numbers, each with up to 20 lot numbers. It is not feasible to paste a formula for each part number manually.
You only need to copy and paste once. for all your thousands of part numbers. I've done this with > 100,000 items before and no issues.
The formula works for an infinite variety of part numbers... here, this is more generic:
=IF(B2="Goods",SUMIFS($C:$ C,$A:$A,$A ,$B:$B,"<> Total"),"" )
Now you copy that down for as much data as you have - and if its the entire set of rows it will work. For several thousand, its not intensive...
Otherwise, the macro is your solution and maintaining it should be the only concern...
Dave
The formula works for an infinite variety of part numbers... here, this is more generic:
=IF(B2="Goods",SUMIFS($C:$
Now you copy that down for as much data as you have - and if its the entire set of rows it will work. For several thousand, its not intensive...
Otherwise, the macro is your solution and maintaining it should be the only concern...
Dave
PS - You copy this formula down (not individually for each part number)
:)
Cheers,
Dave
:)
Cheers,
Dave
....that still involves re-summing a total that already exists......
This version will just lookup the next Total row and give you the relevant number
=IF(B2="Goods",VLOOKUP(D$1 ,B3:C$5000 0,2,0),"")
Place that formula in D2 and copy down
regards, barry
This version will just lookup the next Total row and give you the relevant number
=IF(B2="Goods",VLOOKUP(D$1
Place that formula in D2 and copy down
regards, barry
Incorrect. The formula I provided does not resum any totals.
Dave
Dave
Ah - I see what you mean by re-summing, in that it just needs to be looked up. I responded too quickly :)
Nice and tight, barry.
Dave
Nice and tight, barry.
Dave
ASKER
I appreciate the help, but I think I’m missing something here… I’ve tried each formula, but it seems to die after the first few entries. I attached the entire spreadsheet and yes, I’ve tried with and without the subtotals… also, keep in mind that the other column on the spreadsheet need to stay there…
mmi.xlsx
mmi.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
See Dave, I told you I was missing something... Turns out it's my mind...
You nailed it! Thanks a bunch!!!
You nailed it! Thanks a bunch!!!
You can use a Macro to do that, in you excel spreadshee, add a module in the visual Basic Editor and copy the macro attached, then assign a Shortcut to the macro and run it, it would what you want and will give you a message when finish.
Hope that helps
Open in new window