Sum a column correctly after more info is added by sorting

[Soulscream]

I have a spread sheet that is laid out in sections grouped by dates. I am
trying to create a function that will sum a particular column correctly and
expand even when new information is added and the dates are resorted.

Example: Cell C100 should sum the column A80:A99, but when new information
is added and then the spread sheet is resorted the column will expand to
A80:A100 in which case Cell C101 would then properly total A80:A100 and Cell
C100 would go back to being blank.

I think there may be a way to count between blank cells and on my
spreadsheet Cell A79 will always be blank, and whatever cell in the A column
of the same row as my C cell with my formula (ie. A100 or A101) will always
be blank.

If anyone has any ideas your help would be greatly appreciated.
Thank you.

 

[Pete_UK]

If A80:A99 (and later A80:A100) refer to the same date, then you could
use:

=SUMIF(criteria_range, criteria, sum_range)

where criteria_range and sum_range can be complete columns, eg D
(for dates) and A:A for your numbers, and criteria can be the date you
are interested in (which could be picked up from another cell).

Hope this helps.

Pete

 

[Bernie Deitrick]

In C81, enter this

=IF(A81="",SUM($A$81:A81)-SUM($C$1:C80),"")

and copy down.

HTH,
Bernie
MS Excel MVP

 

[Joel]

Excel automatically does this as long as you insert a new data inside the sum
range. Instead of inserting new data at row 101 insert the new row at row
100. then copy the data at row 101 to 100 and add the new data at row 101
like this

Before
99 55
100 26
101 sum(A70:A100)

After Insert Row
99 55
100
101 26
102 sum(A70:A101)

Then copy from 101 to 100
99 55
100 26
101
102 sum(A70:A101)

Add new Data
99 55
100 26
101 44
102 sum(A70:A101)


If you need to add in the middle of the table then just add a row and you
don't need to copy anything

 

[Soulscream]

See my problem is that I have multiple groups in date ranges. For Example:

A B C
1 50 1/5
2 25 1/5
3 75 1/6
4 100 1/7
5 100 1/9
6 350
7 25 1/12
8 25 1/13
9 50 1/15
10 100 1/16
11 200

The groups of date ranges are never set to how many rows it will take up.
So, in the example above, I need Cell C11 to activate when A11 = “â€, but I
only need it to sum up A7:A10. And tomorrow if I add three more numbers in
that date range I will need it to jump down and have Cell C14 activate when
A14 = Ҡand then total from A7:A13.

Is that possible?

 

[Soulscream]

Sorry, Microsoft doesn't show my spaces correctly. Please see below and
ignore the periods as spaces. Hopefully, this time it will display correctly.

 

[Soulscream]

Sorry, Microsoft doesn't show my spaces correctly. Please see below and
ignore the periods as spaces. Hopefully, this time it will display correctly.

.....A......B......C
1...50...1/5
2...25...1/5
3...75...1/6
4..100..1/7
5..100..1/9
6................350
7....25...1/12
8....25...1/13
9....50...1/15
10.100..1/16
11..................200

The groups of date ranges are never set to how many rows it will take up.
So, in the example above, I need Cell C11 to activate when A11 = “â€, but I
only need it to sum up A7:A10. And tomorrow if I add three more numbers in
that date range I will need it to jump down and have Cell C14 activate when
A14 = Ҡand then total from A7:A13.

Is that possible?

 

[Pete_UK]

You must have other fields which enable you to sort the data that way,
so please give details.

Also, do you add the blank row manually between each block?

Pete

 

[Soulscream]

Yes, there are other cells, but they are unrelated so I didn’t want to type
them all. The way I blank the row is by putting a Saturday Date in the date
column beside where my total will be, but I leave the other cells in that row
blank, so when it is resorted and a new date is added in, it will be placed
before that Saturday date. So, I guess I should have put it in like this:

.....A......B......C
1...50...1/5
2...25...1/5
3...75...1/6
4..100..1/7
5..100..1/9
6.........1/10...350
7....25...1/12
8....25...1/13
9....50...1/15
10.100..1/16
11........1/17...200

 

[Pete_UK]

In that case, and assuming that the Saturday dates only occur on those
blank rows, you can put this formula in C1:

=IF(WEEKDAY(B1)=7,SUMIF(B$1:B1,"<="&B1,A$1:A1)-SUMIF(B$1:B1,"<="&
(B1-7),A$1:A1),"")

and copy this down. This will add up a weekly range of dates, and will
only appear on the row where you have the Saturday date (i.e. the
blank row) - for other dates you will have a blank in column C, as you
have laid it out in your example.

Hope this helps.

Pete