Cumulative Count for Various Cells in a column

Sarah Lundquist

We have to keep track of daily mileage for our fleet vehicles. I am keeping monthly totals which are calculated at the end of the month. They are based on the driver's daily entries. I want the monthly mileage to be cumulative as the month progresses.

But I also want to keep a yearly cummulative count as well. Right now, that count is "0" until the end of the year.

My "Vehicle Info" column has basically all this statistical information we collect. The beginning and ending monthly mileage numbers are basically referencing other cells.

Current Monthly Formula (ending mileage minus beginning mileage):

=SUM([Vehicle Info]22 - [Vehicle Info]24)

Current Yearly Formula (adding the totals of every month's total):

=SUM([Vehicle Info]19 + [Vehicle Info]44 + [Vehicle Info]72 + [Vehicle Info]103 + [Vehicle Info]133 + [Vehicle Info]133 + [Vehicle Info]164 + [Vehicle Info]194 + [Vehicle Info]225 + [Vehicle Info]256 + [Vehicle Info]286 + [Vehicle Info]317 + [Vehicle Info]347)
—————————————
I'm seeing options for column counts where all the row values are together, but these cells are sporadic among several hundred.

I've also seen the row ID and text column additions to the sheet. I guess I don't know how to implement them here.

Any help would be great!

MedaUser

Hi @Sarah Lundquist,

For the Monthly Cumulative, I think what you are looking for may already be setup for you in the parent rows of the months. However, I do notice that January is missing a parent row (header) like you see for Feb, Mar, etc. I would ensure that within the month rows (blue rows) under Business Miles column, you write the following formula and just map the total monthly miles field to it.

• Under Business Miles column in the blue rows:

=SUM(CHILDREN())

• Under "Total Business Miles for [MONTH]" field:

=[BUSINESS MILES]row number of the blue row parent (i.e. May = 131)

For the Yearly Cumulative, I would follow the same process as above, but you'd only have 1 parent row, which is the dark blue.

Let me know if you need further assistance!

Sarah Lundquist

Sarah Lundquist

Something happened and my reply didn't get sent…..

I sent another full screenshot of the collasped version of this sheet. I did make the January 2025 parent row and moved the date down.

A couple more questions I had though:

If I add January parent rows, that will leave a gap in the date column. That is a formula column which I use for the purpose of the multi-year calendar, accounting for Leap Years:

=$Date$1 - (YEARDAY($Date$1) - COUNT($Date$1:Date2))

If there is now an empty cell for every year, how to I continue to use that reference cell at the top and make that work?

Also, the Business Miles column is all formulas as well:

=[End Mile]11 - [Beg Mile]11 - [Personal Miles]11

Does that screw me up at all if I use your method?

Sarah Lundquist

Does that suggestion change at all since the Business Miles column already has formulas in those rows? That column calculates beginning and ending with the driver only having to input the ending mileage. Most of the columns are locked down. I did this so that the end user can't screw things up by accidently changing cells and they also have minimal work to do (the buy-in) their part of this, haha.

Business Miles Formula

=[End Mile]132 - [Beg Mile]132 - [Personal Miles]132

I did make that Parent Row for the first January, just to test some things.

I am attaching another screenshot showing more info. There is also a calculated date formula which runs for several years. Those dates run straight down the column with no break for the dark blue year row. If I add the parent row for all Januarys, will that screw up my date formatting because there will now be an empty cell on the calendar year rows?

MedaUser

I don't think it would mess anything up, but I'm not sure I'm following everything you're worried it might impact. Without trying to explain around your worries, could you try the following formula for cell [Vehicle Info]140?

=SUM(CHILDREN([Business Miles]132))

This skips the step of adding a formula to the header rows.