Calculating Total from selections

[cmo]

Hello everyone,

Can anyone help / make suggestions in achieving the following:

I am trying to create an invoice that summarises the total of the
products at the bottom of the spreadsheet.

What I want to achieve is some way of having various products in a row
which a user can select via some sort of check box (giving the product
a boolean value).
Each product would have a price value attached to it and when it is
selected the price should be added to the total.

This would therefore allow the user to select various products and the
total would be calculated accordingly.

This seems relatively simple in theory although I just don't know how
to do it so any help would be greatly received.

Thanks.

 

[Frank Kabel]

Hi one possible solution:
1. Create checkboxes for each of your products (go to
'View-Toolbars-Toolbox)
2. Link theses checkboxe to cells. (e.g. you have 4 products and you
have linked the checkboxes to the cells A2 - D2)
3. Depending on the state of your checkboxes the cells A2-D2 will
display TRUE or FALSE
4. In the row below the linkes cells (that is A3-D3) enter your product
prices
5. enter the following formlua to calculate the total cost
=SUM(A22*A33)
you have to enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank

 

[Dave Peterson]

This might not work for you, but I'd approach it this way--just use a bunch of
columns (I used 5).

Qty Part Description UnitPrice ExtendedPrice

Leave the qty blank, but enter the part, description and unit price.

In the ExtendedPrice column, you can use a formula like:

=a2*d2
(and drag down)

Or maybe to hide the 0's.

=if(a2=0,"",a2*d2)
(and drag down)

then at the bottom of the sheet, put this in the total's row (say 25):

=sum($a$2ffset(a25,-1,0))
By using offset and putting the formula in A25, you won't have to worry about
inserting a row and having the formula not include a cell.

And same kind of thing in E25:
=sum($e$2ffset(e25,-1,0))

Put some nice borders around each row, center things nicely, add headers to the
top and make sure it fits on a page (or two).

 

[cmo]

Thanks greatly for all the help