excel; sum only items that a user selects 1

[tav1035]

I want to build a chart with each cell that contains an item with a hidden price or value. Then a user can choose the cells or items and the items sum up to a total.
Kinda like a shopping cart in excel.
Also as they choose the cell it will highlight that chosen cell.

 

[SimonDavis]

Hmm. I would seriously consider access for that, but you could do it in Excel - it's a bit messy.

Assume you want to avoid programming? Try one of the followng.

1. Put the description in column a (for example), the price in column b, and have the user choose how many of that item they want in column c. You can then hide the price column, and in column d just put a formula =b1*c1. If they don't enter a number of items, the result will be zero. Put a sum at the bottom of column d for the total.

You can use conditional formatting to highlight the item description - use the logic 'if column d > 1, use format' If you need help on that, post back.

Or, if the user is going to select just one of each item, and you want to use an x or something;

2. Use four columns, here I'll start at row 1, but adjust as neccessary:

A. put the description of the item.
B. put the price
C. leave blank
D. put the following formula:

=if(c1 = 'x',+b1,0)

This will look for an 'x' in column c, and if it finds it, will put the price of the item in column d.

You can then total column d for the final amount.

As in the first, when you've entered all the data, you can hide column b, which won't affect the calculation.

And agin, use conditional formating with the logic 'if column c = 'x'

 

[SimonDavis]

Just to be really clear - in the second example, the user will place the x in column c, next to the description.

 

[tav1035]

cannot get this formula to except...it has an error?
=if(c1 = 'x',+b1,0)

 

[Chance1234]

going down that root, with three columns

A B C
aescripton
brice
c:blank (like above user places x )

and pesuming the first row are headings and for this exampkle you have ten records

try this

=sumif(C1:c10,"x",B1:b10)

that shuold work grand

 

[SimonDavis]

Sorry - I have Portuguese software, the punctuation in formulas is different. If you're still having a problem, look in help for these formulas, they'll give the correct syntax.

 

[tav1035]

That works, But I would like to reformat the columns like this:
SUMIF(C1:F1,"x",B1)
A B C D E F G
aescripton (row 1)
c:Jan. (row 1)
d:Feb. (row 1)
e:Mar. (row 1)
f:Apr. (row 1)

brice (row 2)
c:blank (user places x ) (row 2)
d:blank (user places x ) (row 2)
e:blank (user places x ) (row 2)
f:blank (user places x ) (row 2)
g:Total (Total of row 2) (row 2)
------------------------------------
Decription Price Jan Feb Mar total
clean 100 x 100
sweep 25 x 25
mop 50 x x x 150


I can't seem to get this to work... Any help would be appreciated

 

[SimonDavis]

Understand what you're trying to do. The only problem with this is you are enclosing your working area - what if you want to add April now?

To do exactly what you want, you could modify the formula in the total column to

= sum((counta(c1:f1)*b1)) (change the row number to suit your sheet)

This will count how many of the cells have something in it (so be carefull, it doesn't have to be an 'x'), and multiply by the price.

See how you get on, let us know if you need more info.

 

[tav1035]

I must say that the replys great excellent...
I accomplished the excel worksheet and it works great.
Thanks