Hello, Please let me start by saying...this is amazing! How wonderful that there are people who love Excel enough to help others out. I don't know much about Excel, but I know enough to say that if I could make a living off of learning the program, I would quit my job today!
Okay, with that out of the way, down to the nitty gritty!
I have a 2 column table that tracks the total units sold each month; column headings - Month & Units Sold; first cell in table is H7; the formula will go in I7. I have a 2nd table that tracks all the clients each month; where they are in the sales process; if they purchased, how many units, the product name, the delivery date.
I have a date, in Month format, in cell L1. From cell H7:H18 are dates Jan to Dec, in Month format. What I would like to do is add the total of units sold from the 2nd table, in the current month, which is reset each month, to the 1st table without losing the total from the previous month.
At first I was thinking I could incorporate =month(L1)=month(H7), but I can't make that work. Any assistance would be greatly appreciated!
I hope this hasn't been too confusing. Thanks for your time and consideration,
Lani
Hi Lani,
You have mentioned that the table with the sales data will be 'reset each month'?
This means that a formula pointing to your January column will lose it's data when you reset the source table.
Moreover, The formula you setup may mean that placing February data into the source table would pull February data into the January cell.
Looking at the setup in your sample sheet, I would suggest that manually typing the figure (or copy, paste) into the new months table would be best.
However you might like to consider changing the approach (unless you are stuck with bringing this data in from elsewhere in this format!)
I see you have filters turned on for cells D5 - I5. This means you can filter by any of those columns.
If you added a column after G (slotted in before the "Fit Date") you could then put "Sale Date" in there.
If the entire year's sales were recorded in this table (and not then resetting each month) you could have a formula next to your months, that looks at the entire year list and only totals those where the sale is in the relevant month.
Also, I see that it is only those with a status of "Sold" that are totalled - so a Sale Date can be left out until such time that an item is sold - then it gets counted once that date is added.
I have attached an example for you. If you are after something different, do let us know! (you will note in the sample that the sale dates include Dec,Jan,Feb which dictates what appears in the secondary table)
