• Status: Solved
• Priority: Medium
• Security: Public
• Views: 266

# Calculating sums when the number of values to be added is variable

Hello,

In the following screenshot of an Excel (2010) spreadsheet, all but two columns (C & F) of a bank account register are shown.  The remaining columns are present but have been hidden.
Because there are multiple entries for many of the days, a third column (G) is designated to display daily totals.

The daily totals in the screenshot spreadsheet were created manually using =SUM() but what formula in column G would generate those totals automatically.  The problematic issue is that the number of values for each day is not constant and therefore the formula needs to be able to locate the first entry of the day and then take the sum from that entry down to the last entry of the day.  Which Excel function will do that?

Thanks
0
1 Solution

Commented:
put in g3:
=if(a3=a2,"",sumif(a:a,a2))

If you upload a dummy worksheet (without confidential data) I can test it out for you.
0

IT Pro/DeveloperCommented:
Check if it's something like this
sumif.xlsx
0

Commented:
whoops - try this one:

=if(a3=a2,"",sumif(a:a,a2,f:f))
0

Commented:
Try this:

Keep the "Date" and "Daily Total" column side by side.
Eg: date column "C" and Daily Total Column "D"

Enter all the dates in column C and the resective amounts in column D.

Select column C and D - click on Data and then click on Subtotal.

The first dropdown should be the "date", second drop down should be "Sum" and place a check mark on "amount" as in the screen shot below.

This is how the result will look like.

OR

You can follow this link : (I was not able to understand the entire stuff on the link below). May be it will help you or help another EE get an idea to provide a solutuion that suits your needs.

0

Commented:
@Steve - not sure where your data starts/ends based on your picture, but lets assume the data starts on row 2 and goes down to row 1000 (you can certainly change the ranges, as needed, then the formula in Column F would be:

[F2] =IF(ROW()=MAX(ROW(\$C\$2:\$C\$1000)*(\$C\$2:\$C\$1000=C2)),SUMIF(\$C\$2:\$C\$1000,C2,F\$2:F\$1000),"")  <- then hit CTRL-SHIFT-ENTER to confirm array function, and it will then look like:

{=IF(ROW()=MAX(ROW(\$C\$2:\$C\$1000)*(\$C\$2:\$C\$1000=C2)),SUMIF(\$C\$2:\$C\$1000,C2,F\$2:F\$1000),"")}

Of course, you can then copy that formula down...

The first part of the IF statement checks to see if this is the LAST occurrance of the date in the list:

ROW()=MAX(ROW(\$C\$2:\$C\$1000)*(\$C\$2:\$C\$1000=C2))  <- gets the max row where the date is equal to the date being examined, then compares to current row

See attached demo worksheet I built to mimic what was in your picture.

Enjoy!

Dave

The second part is fairly straight forward, and you may recognize SUMIF - testing column C - dates, against the current date, then sum column F
sumTotals-r1.xls
0

Commented:
@Steve - there were some other viable solutions, if your data will always be sorted, whereas my solution is unique & correct for that and if the data can come out of sequence.

I have to admit, I may have overkilled the formula, otherwise :)

Dave
0

Commented:
Maybe it could be as simple as this:

cell G126: =if(C126<>C127,SUMIF(C:C,C126,F:F),"")

Fill down this formula for the entire list.

Regards,
Davy
0

Commented:
You could also use a pivot table for a summary by day (or month, or year etc)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.