0

I need a worksheet function or formula or a VBA user defined function to get a sum of a number list and the result will be at the top of the list, not bottom of the list like Autosum feature in excel. Also there will be many number lists in two seperate columns so that I don't want to write the formula for each number list. Also in the image the yellow cells will be blank in real life.

in the image I tried to give an example. I hope it will helps you.

  • I am trying to get a formula that works in reverse manner of autosum function. – Omer Gokhan CIFTCIOGLU Mar 25 '17 at 11:36
  • What is reverse of autosum? No sum at all and not auto? – ejbytes May 3 '17 at 5:58
  • I read some of your comments on Answers posted. I can't imagine how someone would be able to give a solution on this. Mindblow. I read that you have some code. Why don't you post your code and someone can critique it for you. – ejbytes May 3 '17 at 6:01
0

Use =SUM(START:END), Copy/Paste and/or a Macro

To save typing a formula for each list you could in your first 'result cell', you can use =SUM(B4:B10). For example:

enter image description here

Then copy and paste this formula into the other 'result cells', and the formula will update the references. This assumes that the lists you are summing are the same length, of course.

If you are running this repeatedly - eg for reports - recording a macro will help automate the process.

When you record a macro, the macro recorder records all the steps in Visual Basic for Applications (VBA) code. These steps can include typing text or numbers, clicking cells or commands on the ribbon or on menus, formatting cells, rows, or columns, or even importing data from an external source, say, Microsoft Access.

If you want to write your own macro, it's worth looking at the .Select and ActiveCell.Formula[A/R1C1], along the lines of:

Worksheets("yourworksheet").Range("ResultCell").Formula = "=SUM(Range1:Range2)"

Replacing and inserting ResultCell as necessary.

Further Reading

There are plenty of resources out there; you might want to consider a resource on formulae (from wikibooks). From that page:

Formulas used in spreadsheets, automatically process data how the user see fits. The formula takes data from certain areas in the spreadsheet, processes it, and places the output into the new area of the spreadsheet based on where the formula is written. The formula can be as simple as "=SUM(A10,A11)" (which takes the information in the 10th and 11th cells of row A and outputs the sum), or as complex as the user wishes to make it. The functions used to create the formula (such as SUM), are predesignated by the spreadsheet software.

It's worth doing some background reading as it can make solving future problems much simpler.

Microsoft have a page on different ways to add values too; but SUM should do you fine in this case.

  • My friend, I am not talking as simple as like that. I know excel basics. Let me give you an example; – Omer Gokhan CIFTCIOGLU Mar 25 '17 at 12:19
  • I can calculate the sum of the all reult cells in the image by selecting all blank cells, then press 'Alt + Enter' to activate autosum feature, then press 'ctrl + enter'. This gives me the sum of upside number list. However I want to sum downside list, but I don't know the formula. I don't want to write sum function for each number list. I need to handle all of them at once. – Omer Gokhan CIFTCIOGLU Mar 25 '17 at 12:25
  • @OmerGokhanCIFTCIOGLU Apologies, it really wasn't clear from your original post what you actually wanted. Is there any reason why you need to do it this specific way? – bertieb Mar 25 '17 at 12:34
  • Yes actually, in my company the format of the file has been designated as the totals of many data list are on the top of the data, and I am wanted to calculate the total values every day. It takes a huge amount of time. If you find a way to do that in an easier way, ıWill appreciate you. It can be VBA code or etc. – Omer Gokhan CIFTCIOGLU Mar 25 '17 at 12:49
  • 1
    Super User isn't a code-writing service ;) A solution specific to your needs wouldn't necessarily help others. I have included information on macros and the VBA that will let you write it tailored to the cells that you need. – bertieb Mar 25 '17 at 12:59
0

Create this Macro

Sub MacroSumDownward()

' Keyboard Shortcut: Ctrl+d
'

    Selection.FormulaR1C1 = "=SUM(R[1]C:R[99999]C)"

End Sub  

You can record any Macro and assign a shortcut to it and copy the above:
Selection.FormulaR1C1 = "=SUM(R[1]C:R[99999]C)"
R[99999] to select whatever cell in the column and include it to the sum you can change it as you have maximum rows in your Data,
and delete any other instruction, just keep

Sub Macro()
Selection.FormulaR1C1 = "=SUM(R[1]C:R[99999]C)"
End sub  

when you want to run it, just select the cell above each column (all at the same time) and use the shortcut you have assigned to the Macro(I used Ctrl+d it will do the Sum
It is better to use different column unless you have a fixed number of rows (or just keep more space than the maximum rows you wrote in the Macro)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.