How to calculate numerous clicked cells?

[chaser7016]

I have a spreadsheet where say column A(i.e. apples) is subject matte
and column B is the price of subject matter; columns of subject an
price extend down to cell 30. I, also have column C(another subjec
matter) and column D(the price of C subject matter) and thes
subjects/figures also extend down to cell 30.

I want to be able to click on the price of B1 and D1, have exce
highlight(keep prices highlighted) each clicked price and the
calculate it. B1 and D1 are of course just examples, as it could b
any of the prices that needed to be calculated; i.e. B1 + B29 + D1
+B10 + D3, etc, etc....

Well thanks to anyone who can help or comment on my thread!

Cheers, Chaser

 

[KellTainer]

Did you know Excel has an inbuilt autocalculating function.

When you select two cells, notice at the bottom of the applicatio
there will be this text, SUM=??, besides the READY Text.

You can even right click on the sum thingy and choose other functions

 

[Guest]

Further to Kell's post, use the <Ctrl> key to select multiple cells which can
be noncontiguous. They will all highlight this way. If the Status bar is not
shown, you can display it through: Tools > Options > View tab> Status bar
checkbox.

Right-click the little window that displays the calculation to allow you to
change the function type - can be Average, Count, Count Nums, Max, Min, Sum.

Regards,
Greg

 

[chaser7016]

Awesome, that is a great solution! Thanks!

Now I wonder when I done summing up all the prices for purchase A, i
the SUM that appears at the bottom can appear in a cell within my shee
automatically? I want to then SUM up a seperate purchase(purchase B) an
have that total appear within it's own seperate cell as well.

Well thanks for all help and comments!

Chase

 

[Guest]

It sounds like you are creating something like a "shopping cart" setup. You
might want to consider using check boxes adjacent the prices you want to sum
instead. You wouldn't be summing selected cells but the cells adjacent
checkmarked check boxes. Just a thought.

Responding to your request, the following is my suggestion:

1. Put two command buttons on the worksheet and assign the appended two
macros. Change the captions of the command buttons to "Sum" and "Clear"
respectively. Alternatively, do this with tool bar buttons added to the
Worksheet Menu Bar. If you choose the second option, then you will likely
want to add them programmatically on wb_open and make them Temporary else
they will still be there when another wb is open and cause problems if
clicked.
2. Type "Results:" in the desired cell in the first row (note the colon
":"). It must be to the right of your data.
3. Now select the prices you want to sum using the <Ctrl> button to select
noncontiguous cells.
4. Click the first "Sum" button. The result will be pasted to the right of
either the word "Sum:" or the previous result.
5. Click the "Clear" button to clear the results.


Sub SumSelection()
Dim ws As Worksheet
Dim c As Range
Set ws = Sheets("Sheet1")
Set c = ws.Cells(1, Columns.Count).End(xlToLeft)(1, 2)
c.Value = Application.Sum(Selection)
End Sub

Sub ClearResults()
Dim ws As Worksheet
Dim c As Range, c2 As Range
Set ws = Sheets("Sheet1")
Set c = ws.Rows(1).Find("Results:")(1, 2)
Set c2 = ws.Cells(1, Columns.Count).End(xlToLeft)
ws.Range(c, c2).ClearContents
End Sub

Regards,
Greg

 

[Guest]

Point 4 should say:
4. Click the "Sum" button. The sum of the selected cells will be pasted to
the right of either the word "Results:" or the previous result.

Greg

 

[Guest]

Chaser,

Sorry, minor change required for ClearResults routine. Should be:

Sub ClearResults()
Dim ws As Worksheet
Dim c As Range, c2 As Range
Set ws = Sheets("Sheet1")
Set c = ws.Rows(1).Find("Results:")(1, 2)
Set c2 = ws.Cells(1, Columns.Count).End(xlToLeft)(1, 2)
ws.Range(c, c2).ClearContents
End Sub

Greg