Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

A formula to sum up rows within the same date range

0

Hello, please could some experts help with my work? I've included an example file with colours to help explain.

I manually add in a formula (column K) to calculate the sum of a series of rows (column F) which belong in the same date range (column H). Conveniently they are consecutive rows so I usually go through spotting the changes in the dates.

This takes me ages to do as I have loads of these spreadsheets, so would anyone know of an easier way to do this please? 

excel
MS Office Version: 2016
posted by: c50 (rep: 4) on Fri Aug 14, 2020 at 9:05 am
Answer
Discuss

Answers

0
Selected Answer

Hello c50

I used your sample file to create a macro which I think does what you are looking to achieve. You can check out the result in the attached file. In your sample file I copied sheet1 so the original data didn't get messed up while testing. The macro will give you your totals in Column "K" but does not enter the formula to the worksheet. There are comments in the macro for how to tweak it to have it insert the formula. To check it out, open the attached file and run the macro ( Sum_Amounts_In_ColF() ) on "sheet2" The macro doesn't reference a specific worksheet so it will run on whichever sheet is active (selected)

Cheers   ;-}

UPDATE: Aug. 29/20

Replaced original file with file with corrected macro code

posted by: WillieD24 (rep: 557) on Sat Aug 15, 2020 at 11:11 am
Discuss

Discussion

Thank you very much! I've not used a macro before but it's amazing!

I really like your comments on what is happening. When I run the macro, the value seems to be wrong though and I can't quite understand why from inspecting the code. I wish I knew coding! What does the numbers after startrow and endrows denote?
c50 (rep: 4) Aug 28, '20 at 7:02 am
Ooops, my mistake, sorry(I was rushing). My original code as written was only adding the first and last values in the group. I failed to include Range in the formula. Here is the fix: (change this one line of code to get the correct results)
Change the line 
Cells(totalRow, 11) = Application.WorksheetFunction.Sum((Cells(startRow, 6)), (Cells(totalRow, 6))) * 12
to    
Cells(totalRow, 11) = Application.WorksheetFunction.Sum(Range((Cells(startRow, 6)), (Cells(totalRow, 6)))) * 12
If you would prefer to have the formula entered in Col K (not just the sum) then insert a single quote mark ( ' ) at the start of the above code line and remove the single quote mark from the line
Cells(totalRow, 11) = "=SUm(F" & startRow & ":" & "F" & totalRow & ") * 12"


The numbers after startrow and endrow ( 2 & 3) are the intial values where the code begins. "startrow = 2" is row 2 of the worksheet; "endrow = 3" is row 3 of the worksheet. After a block of common dates has been summed the these values are incremented to start checking on the next row of data.

I also replaced my original file (above) with one which has the corrected macro code.
WillieD24 (rep: 557) Aug 29, '20 at 10:20 am
Thank you very much for explaining to me - after a day going over and over, I'm starting to learn and it's very interesting.

I also went back to the original spreadsheet and saw the other formula in the comments - after understanding your instructions I found it works. It's amazing! 

I just need to learn how to copy this macro into the other multple spreadsheets and adjust the columns I have to work through. They are massive spreadsheets so you are a life saver!! Thanks WillieD24
c50 (rep: 4) Sep 1, '20 at 8:09 am
Hello c50,
If the spreadsheets you refer to are separate/individual Excel files it is really quite simple. First, open the spreadsheet with my code in it. Press Alt + F11 to open the VB Editor (VBE). In the upper left pane (VBA Project) click on “Module1” to view the (my) code. It’s now your choice if you want to open all of the other files or just work with them one at a time. For each of the file(s) you want to add the macro to do the following: i)             select all of the (my) macro code and press Ctrl + C to copy it; ii)            for the file you want to add the code to, in the VBA Project pane select (click on) the file name; then in the header row click on “Insert” and the click “Module”; iii)           click inside this new module window and press Ctrl + V to paste the code in. To adjust the columns that is done in the Cells element: the first component is the row number, the second component is the column number (1 is A, 2 is B, 3 is C, etc) iv)           click the save icon (top left) and then close the module window by clicking the “X”.    Done.    Repeat these steps for each of the remaining files.  
Cheers   ;-}
WillieD24 (rep: 557) Sep 1, '20 at 10:31 am
As I mentioned Aug. 29 the file attached to my Aug. 15 post has the corrected code. In the code you will see lines in green – these are comments and ignored by the macro because of the single quote ( ‘ ) at the beginning. If you prefer to have the formula in the cell and not just the total, then uncomment (remove the ‘ ) the line of code as noted in the macro.   If the spreadsheets are different Worksheets in the same file let me know as that will be a bit different. (still easy just a bit more work)
WillieD24 (rep: 557) Sep 1, '20 at 10:31 am
Thank you WillieD24! I've been copying and using your macro on many spreadsheets at work now (with slight column adjustments as i did omit a few redundant columns due to personal data).

My work has been so much easier since! I wish I could do what you could do :)
c50 (rep: 4) Sep 9, '20 at 10:01 am
When I get more time, I shall have to come on this site to learn more about excel :)
c50 (rep: 4) Sep 9, '20 at 10:02 am
Add to Discussion
0

You can solve your problem with the use of the SUMIFS function, for example as shown below.

=SUMIFS([Sum range],[1st Criteria range],[Criterium],[2nd Criteria range],[2nd Criterium], etc, etc)*12
[K2] =SUMIFS(F:F,H:H,H2,I:I,I2,J:J,J2)*12

This formula returns the correct results for the 6 examples in your sample but I couldn't quite figure out which are the relevant criteria. Perhaps the End Date isn't, and perhaps the Currency is. You may have to adjust the formula.

The formula will return the same result for each group of rows. For example, if you copy it from Rows(2) (for which it is designed) down to Rows(4) all cells will show the same result. There is no easy way to avoid this. Therefore you would need to work on the results.

If you need a total in the column the extra values have to be eliminated. One way to achieve this would be with a helper column. Paste the formula below in the helper columns Row #2 and copy down. Change the referenced column (here column K) as required.The column can be totalled using the SUM function.

=IF(K2<>K1, K2,"")

If you don't need a total, just for visual effect you could hide the superfluous results using conditional formatting and the same logic. Use the formula below to determine the cells to format and format their font colour to turn white (colour of the background) so that the number becomes invisible.

=K2=K1

One easy way to apply this format is to select all cells to which the format is to apply, e.g. K2:K1700, and type the formula so that the first referenced cell is the active cell (that is the first cell of the range you selected) and the second one the cell above it. You can later adjust the range in the CF Manager by changes the Applies To range address.

posted by: Variatus (rep: 4889) on Fri Aug 14, 2020 at 8:45 pm
Discuss

Discussion

Thanks for your help, I'm trying to understand your formulas as I can't seem to get them working.

Only the totals (column F) and Start Date (column H) are needed because I need to sum up all those totals within the same group of start dates. I coloured each group for visual ease

I'm thinking that I need an "sumif" formula to compare each date with the next and than somehow add those totals together if the date matches, from your answer
c50 (rep: 4) Aug 28, '20 at 5:55 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login