Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
Sub sumselectedcells()
For Each c In Selection ms = ms + c Next c MsgBox ms range("b2")=ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Fergal" wrote in message m... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
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" wrote in message m... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
"Fergal" wrote in message m... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
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, "Fergal" wrote in message m... "Fergal" wrote in message m... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
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. "Alan" wrote in message ... 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, "Fergal" wrote in message m... "Fergal" wrote in message m... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total price...
Many Thanks Alan, I'll give that a go and see what happens. Thank You for
being so patient. "Alan" wrote in message ... 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. "Alan" wrote in message ... 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, "Fergal" wrote in message m... "Fergal" wrote in message m... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtract a discount from total price? | Excel Discussion (Misc queries) | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
Pivot table - total sell price | Excel Discussion (Misc queries) | |||
calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
calculate price * quantity = total amount in a row of excel forml. | Excel Worksheet Functions |