Solved

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

Posted on 2011-10-11
185 Views
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

LVL 10

Expert Comment

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

LVL 48

Expert Comment

Check if it's something like this
sumif.xlsx
0

LVL 10

Expert Comment

whoops - try this one:

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

LVL 10

Expert Comment

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

LVL 41

Expert Comment

@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

LVL 41

Expert Comment

@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

LVL 3

Accepted Solution

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

LVL 85

Expert Comment

You could also use a pivot table for a summary by day (or month, or year etc)
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…