Guest
[Entry]
"An unusual reqeust, certainly.
I'm working on the assumption that column M has your quantity in it, and this is what you mean by ""number of products ordered"", and column I has your product names in.
I also read it to mean that you want to do this for one product at a time.
In a new column to the right, say in R1, put the name of the product you are looking for (so you can easily change this later to each one in turn). In R2, you need a formula to SUM the quantities in all rows down to and including this one IF the product is the same as the one chosen. Something like this:
=SUMIF($I$2:$I2,R$1,$M$2:$M2)
Notice that with the two ranges they start with a fixed reference (using $ signs) and end with a relative one (no $ on the row number, only on the column)*.
Copy this formula all the way down column R (drag the cell handle, or double click it, or use CTRL-D, whatever you prefer).
So now you have a running (aka cumulative) total for a given product, but the total is also repeated on rows that do not relate to the chosen product. Not ideal, so let's wrap the formula in an IF to suppress the ones we don't want to show up in your chart.
=IF(I2=$R$1,SUMIF($I$2:$I2,R$1,$M$2:$M2),NA())
That NA() at the end will return the ""error"" of #N/A - this will make sure that those points don't appear in your chart. Copy down again.
Now select your date values (not the whole column) and your new cumulative totals and insert a scatter chart with lines (and points if you want.
Yes, a scatter chart (also called an XY chart), NOT a line chart. You need your dates to appear spaced proportionally to time, not just evenly like any other categories, which is what a line chart would do.
Now, if you change your selected product in R1, your numbers should update and the chart too.
If you want to show multiple products you need to create multiple columns like this, so copy the formula across (which is why we made the columns absolute here * above, which was not strictly necessary to make that part work). Chart the multiple columns as multiple series and job done!"
|
