Formula Help

[handysmurf]

I am trying to build a formula so that I don't have to manually enter cell references. Bottom line there are entries for payments and expenses. Those are totalled by day, week, month, and year.

I have a column that contains a code to delineate each summary. If I can figure out one with help, I can figure out how to do the rest (I think) so lets just focus on one, the daily.

Here is a link to a portion of the spreadsheet: https://drive.google.com/file/d/1G5iH1cB1jzX8xicn111jPmbZR4dl2rGq/view?usp=sharing

So the "D" in the third viewable column is the code that indicates the summary row is for the Day. The number of entries between daily summaries vary, hence the need to manually enter cell references.

That is what I would like to do is come up with a formula that automatically sums the second viewable column from row 14:19.

Thanks in advance for any help.

 

[BlueHornet]

SUMPRODUCT could be your friend for this type of summarizing.

For example: =sumproduct(( $A$1:$A$1000 = $A1) * ( $B$1:$B$1000)) will give you a running total of total Daily spending items that are carried in the B1:B1000 range of the formula. You can adapt that to view
=sumproduct( and( Month( $A$1:$A$1000) = Month( $A1), Year( $A$1:$A$1000) = Year( $A1)) * ( $B$1:$B$1000)), etc.

I'll leave the exercise of figuring out Weeks, Quarters or other measures to you.

Note that much of the formula deals in full absolutes: $A$1:$A$1000 and $B$1:$B$1000, for example, while the single-cell references are only absolute as to the column. That's by design, so that as you copy the formula down through the 1000 rows (or whatever your range happens to be), then the row values will change to review "all of that range" compared to "the value in this single cell". You'll get the hang of it soon enough.

Note that this formula will duplicate the "total daily amount" for each row of a multiple-row day, so you may want to make the formula show blanks when, say, $A2 = $A1. If that's confusing then feel free to ask about that later.

 

[handysmurf]

I am totally not following your formula, sorry. Probably wrong but it would appear that that would still require me to manually enter the cell references? I am summing each day. Then summing days for the week sum, then summing the weeks for monthly, and monthly sum for the yearly. But I did figure it out using some extra rows and columns (which can be easily copied, are dynamic, and can be hidden later) and the indirect formula. Probably went the long way around. I'll just put another photo.

https://drive.google.com/open?id=1gDXKgUkqynY149wRQovJHahTSmYBwV2F

I will read your post a couple more times and study how the formula works. I'm sure there is a better formula than what I did, probably nested all into one, that would leave less clutter but at least now I have a workable solution.