Autosum Categories???

[xn1x]

Hello,

I've been trying to figure out a way to autosum categories. Let's say it is for furniture. I have 2 sheets.

One sheet contains the pictures of the furniture, model numbers and totals for each piece of furniture. Kind of like a summary page of how well each piece is doing so far.

The second sheet will only contain the date, model number, and total sold for that day. Basically, a list of items sold so far and is update at the end of each day.
Example:
Jan 1, 2005 | f101 | $1200
Jan 1, 2005 | f293 | $4738
Jan 2, 2005 | f383 | $4747
Jan 2, 2005 | f101 | $2323
Jan 2, 2005 | f293 | $1465
Jan 3, 2005 | f101 | $1500

Now using the example above if I want to automatically sum up model# f101 and display that sum on the 1st sheet in the proper cell... How can I do it? I want to take all the "f101" entries in sheet 2 and sum them up showing the results in the proper cell on sheet 1. I think vlookup would work but I don't know how to write the formula for this.

[Kieran]

You can use the sumif function

Try (on sheet2)

=SUMIF(Sheet1!B:B,"F101",Sheet1!C:C)

Change the reference to f101 as appropriate, or you could use a reference to a cell that contains the value.

[xn1x]

Thanks, That seemed to work just fine. Do you know of a way it could automatically pick up the model numbers instead of manually entering them in the formula??? Thanks again for your help

[Kieran]

The easiest way to accumulate the totals is to use the Pivot Table feature.

Go to the data menu, pivot tables and charts and follow the wizard.

The columns shoud be the model no, and the data field is the sum of the daily sold total.
This method will automatically identify the differing model numbers and give totals.
Note however that that the pivot table will not automatically recalculate the totals as data is added or modified. YOu will ned to use the refresh procedure, and if the data (as I expect) keeps growing, the pivot table source data will need to be adjusted.
This can be done as decribed in http://www.exceltip.com/st/Automatic...rence/118.html.