Difficult excel formule question, is this even possible?

Question

I use Excel in my business, we sell products online, every day when we send our orders out (using excel) we copy and past all the orders onto a excell worksheet. we have 1 worksheet for each month and that sheet list all the orders and products that were shipped to each customer. I need a way to add up all the products that shipped each month. below is an example
DAY Product QTY
1 MB1 3
2 MB5 1
3 MAN 2
4 MB1 1
5 MAN 9
ETC

then on another worksheet I would have a list of every product we sell and somehow add up all the products sold that month for each item.

IS this possible ?

Answer 1

Say you had 100 lines of sales on Sheet1. On the totals sheet type:

=SUMIF(Sheet1!$B$1:$B$
100,"=MB1",$C$1:$C$100)

This will total all the MB1 products in that month. You can then do the same for each of the different items.

Answer 2

This can be easily done by using a pivot table, but first we need to do some minor rearrangement to your current data table -

1. In each sheet, insert a column to the left & call it "Month". For the Month of January, you enter 1 all the way down on the sheet, Feburary 2...& so on

2. Now combine all of your monthly data into one big master data sheet by copying & pasting each monthly data to the bottom of 1st tab (so now you have Month, Day, Product, Qty in the headings - under Month you have 1, 2, 3..., under Day you still have the original 1, 2, 3...)

3. Highlight column A through D, click on "Data" in tool bar, select "PivotTable and PivotChart Report" > Next > Next > Layout > Now drop the "Month" into "Column" and "Product" into "Row" and "Qty" into "Data" > it will show "Count of Qty" > double click on it & change to "Sum" > OK > OK > Finish

4. A finished pivot table will appear on a separate page with all the total automatically calculated for you

Answer 3

Yes, what you want to do is pretty much what Excel is meant for. Generally in a spreadsheet you want to capture all the information once and then do different analyses. For your case, you'd probably use a Pivot Table, which allows you to add up rows based on different columns of interest. Anyway, Excel can be tricky, so I'd suggest searching for templates that already do what you want. Even the Microsoft site probably has some free templates to get you started. Otherwise, just pay any geek in your family to set this up for you.

Answer 4

Well, if I understand you, first you'd select all 3 columns and sort by "product" (Data, Sort) then subtotal (Data, subtotal).

You wouldn't need a separate worksheet.

Scott

Answer 5

That is good.
You are going to use Excel as it is, You will need Functions along with some VBA macros to do this.
I am ready to do all what you need here

Just mail me to see what we can do for it

Enjoy my profile, I am the VBAXLMan