Hello. I only know the basics in excel - pls help me with this one. I have 3 columns, item # (e.g A,B, C..etc),description, Unit price.

I would like to create an option box such that I can enter 2 or more item #s and it will automatically sum up the amount.

it is somehow similar to below

option 1

: A pcs: 300 Price: _____

: D pcs: 150 Price: _____

total Amount:

option 2

: D pcs : 200 Price: _____

: E pcs : 121 Price: _____

: Y pcs : 201 Price: _____

total Amount:

thanks..

You have three columns and lets say that row 1 contains your column names and your first set of items starts in row 2 so in the 4th column insert your piece count and in the 5th column insert the formula:

E2=if(D2<>"",D2*C2,"")

Meaning that if you enter no data (piece count) next to an item then it will display no result otherwise it will calculate the total cost of the amount of pieces per the unit cost.Now, lets say that you only have items listed down to row 26 then in row 27 you enter the formula:

E27=SUM(E2:E26)

You could alternatively enter the formula:

E1=SUM(E2:26) instead to show the total at the top.

This would show the total cost of all items where you inserted a piece count.To go a step further; you could use your sheet as your data base and use a separate sheet as an invoice template where it would use lookup formulas geared off of the item numbers to bring in the data from your data sheet and similarly calculate the total costs.

Excel ('97 anyway) comes with a template for this. Go to File, New, Spreadsheet solutions, Invoice. I have not looked but MS likely has many similar templates available on thier site.

HTH

Bryan

Ask Your Question

Weekly Poll