Excel spreadsheet - sum using highlighted cells containing formulas

Another option is if you highlight the cells

then click
 alt =

It will autosum and put the number under those cells.

But is sounds like you may want something a little more complex which will require a touch of VBA coding.  

Can you describe what you want in a little more detail (maybe an example workbook)?

• right click your sheet tab 
• view code
   
• copy and paste in the code below 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng1 As Range
Set rng1 = Intersect(Target, [a1])
If Not rng1 Is Nothing Then Exit Sub
[a1] = Application.WorksheetFunction.Sum(Target)
Cancel = True
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:

In code window of sheet, paste following code :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Total As Double

    Total = Me.Application.WorksheetFunction.Sum(Selection.Cells)
    Me.Cells(1, 1) = Total
End Sub


'In cell("A1") it will give you total of selected cells.

Example spreadsheet is attached.
What I am seekeing is:
Row 7 and 9 and 13,14,15 and 17,18,19 will be hidden (currently unhidden)            
Postcode Numbers entered in Cells C3 - I3 select 2 rows from same postcode from - 13 & 17, 14,& 18, 15 & 19.                                          
% entered in Cells C3 - I3 adjust the calculations in rows 7 and 9                  
When the cursor is rolled across each age group (RH button held) to highlight each age group, background colour to change  (see H6 to M9) and the sum of the H7:M7 plus H9:M9l to be shown in L4. While cells are highlighted, result remains in cell L4.                        
Doubleclick on highlighted cells to revert background to white and result to zero            

Thanks for your interest.                                                                                                                  

Thanks Brettdj,

Copied code as suggested - but nothing happened.

Perhaps it is because I have amended the example spreadsheet - latest is version 3.
Version 3 is attached here again

Thanks VipulKadia:

Almost working!!! Brilliant.
Please see attached example - version three with code.
The numbers to be summed are now hidden in row 7 and row 9.
Is it possibloe to set up the spreadsheet in some way so that highlighting simply the age groups will complete the sums for each?

Thanks,

Greg

Hi gregfthompson,
Attached file contains the solution.
In this file, whenever you select a cell or range in Row-6, you will get result in Cell('L4').
Hope this will help you.

Thanks VipulKadia:

Can you extend this so that whatever cells are highlighted in ro6 and 8 the corresponding numbers in rows 7 and 9 are summed in L4?

Thanks heaps,

Greg

Please find the attached file.

Hope this is what you want.

Very close.

But please look at attached and see how with row 7 and 9 hidden, when cells in row 6 and row 8 are selected togetehr there is no total.

Thanks heaps.

Actually if you want to select row 6 & 8 then select it using CTRL key. If you will select these rows using mouse drag then row7 also gets selected and that's why it is not giving total.

What I am seeking to do is to hide row 7 and 9 (locked) and only allow access to row 6 and 8.

Is this possible?

Thanks for all your help so far. If it's not possible then I'll have to find another way.