Total price...

[Fergal]

Would it be possible to make a formula that would give the total of several
highlighted cells?
For example in a price list I would like to be ablt to select several item
prices and have the total price displayed in another cell.
Many Thanks for any replies.

 

[Don Guillett]

Sub sumselectedcells()
For Each c In Selection
ms = ms + c
Next c
MsgBox ms
range("b2")=ms
End Sub

 

[Alan]

With this in 'This Workbook' in Personal.xls you get Sum, Average, Count etc
etc displayed in the Status Bar.
I didn't write it, I got it from theses groups. I've had it for years and
I've found it very useful indeed.
I dont know who did write it, but thank you whoever you are.
Regards,
Alan.

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
Application.DisplayStatusBar = True
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Round(Application.Average(Target), 2) _
& "; " & "Count=" & Application.CountA(Target) & _
"; " & "Count nums=" & Application.Count(Target) & _
"; " & "Sum=" & Format(Round(Application.Sum(Target), 2), _
"#,##0.00") & "; " & "Max=" & Application.Max(Target) & "; " _
& "Min=" & Application.Min(Target)
End If
End Sub

 

[Fergal]

Would it be possible to make a formula that would give the total of
several highlighted cells?
For example in a price list I would like to be ablt to select several item
prices and have the total price displayed in another cell.
Many Thanks for any replies.

Many Thanks to Don & Alan for their prompt replies, but I'm afraid I don't
understand any of it, as I'm very new to Excel.
Maybe somebody could explain it in more detail, or perhaps point me to a
tutorial of some kind where I could try to understand what is needed,
Many Thanks

 

[Alan]

Both of these replies are using VB code. It's a way to automate tasks
(Macros) and to do lots of things that can't be done with worksheet
formulas. If you Google 'Excel Macro' you'll find lots of tutorials etc.
It's a bit daunting at first but it does get easier when you use it a bit
more and gain experience.

To make the code that was posted work, in a new worksheet, (Book1) :-

Go to 'Tools' on the toolbar, select 'Macro', select 'Record New Macro'
In the box that appears look for 'Store macro in'
In the dropdown list that says 'This Workbook', select 'Personal Macro
Workbook' > OK
Click the blue square that says 'Stop Recording'

The purpose of this is to create your Personal file that's available in all
of your workbooks. Any macro stored in here is available to any workbook,
one stored in 'This Workbook' is only available in the particular workbook
you put it in.

Now press Alt and F11 together, what you can see is the VB editor.
Top left under the toolbars you'll see 'VBA Project(Book1)'
Below that is 'VBA PROJECT(Personal).xls
Just underneath this is 'Microsoft Excel Objects
Click the + sign to expand the folder and you see 'Sheet1(Sheet1)' and below
that 'ThisWorkbook'
Double click 'ThisWorkbook' and in the large empty window in the middle of
the screen, copy the code I posted and paste it in. Make sure the message is
in full screen mode to avoid any text wrap.
Press Alt and F11 again to close the VB editor.
Save the file.

Now put a few numbers in a few cells and select them all by holding down
Ctrl while you select them, you should see the information on the status
bar, bottom left.

Regards,
Alan,

 

[Alan]

Typo on my part.

"To make the code that was posted work, in a new worksheet, (Book1) :-"

I meant a new workBOOK, sorry. When doing anything like this for the first
time it's always best to do it either in a new workbook or a copy of your
existing file.

That way if anything goes wrong there's nothing lost, you just try again and
don't risk screwing up any previous work.

 

[Fergal]

Many Thanks Alan, I'll give that a go and see what happens. Thank You for
being so patient.