I have a list of 2012 invoices by part number and I need to calculate the difference between the highest price paid per part and the lowest price paid. I then need to multiple this difference to show a maximum potential savings if we were to purchase all parts at the lowest price (under the assumption we purchased all at the highest price). I have a way to do this using [=Max()-Min()] and then [=Count()*result of first formula]. The problem is that I have a list of 30,000 part numbers averaging 4-5 invoices each. Is there a way to use =SUMIF or some other function that will allow me to copy the formula all the way down the column, but only make the calculation on rows with matching part numbers? Once all the calculations are done, I will =SUM the entire "total potential savings" column to present our possible savings (we know why there is price variance and want to implement a plan to correct it and want to present how much we could possibly save by doing so)
Here is a sample of my spread sheet currently
\1
My current formulas
\1
Thanks!
Bookmarks