vlookup? Index? to sum rows?

[handysmurf]

OK lets see if I can give the relevant details or close enough to work ...

I have a spreadsheet in which I sum information daily. On the row for day end there is a cell in which I enter a D. I also sum week ending, designated with "WE" in that cell. Also, Monthly designated by "M" and finally Yearly designated by "A".

The number of rows for each day varies and at the moment I have to do a find/replace on each summary row for the rows to sum. For daily the top row changes. for the others each row summed changes. So I can't sum any by a regular number of rows.

Usually the only constant is that some dailys have a week ending, monthly, and annual summary rows following. (the constant being that 95%+ fo the time WE, M, and A will be in rows directly beneath each other)

I would like to do a lookup or index ... or whatever formula command would work best, to get it to find the rows that need to be summed. In the case of the Daily find the top row, WE sum the daily rows for that week, Monthly to sum the WE rows for that month, and annual to sum the Monthly rows for that year.

Thanks in advance....

 

[steve the fish]

You would need to supply a sample of what you have and what you require. It's not possible, in my head at least, to visualise your set up.

 

[handysmurf]

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

I'm happy to post the image but I don't know if that is allowed.

 

[steve the fish]

You can post images but they arent very helpful ususally. For me to test a solution i need something that can be copied and pasted into excel. I cant do that with a picture.

 

[steve the fish]

Ive looked at the image. I reread your post. I still have no idea what you are after. Most of the stuff you mention in your post isnt in that image.

 

[handysmurf]

So in some cases row 10488 might be followed by a WE summary row, and a monthly summary row

and yes ... to make things more "fun" the days cross midnight so one "day" will have two different dates in most cases.

So basically I want a formula to sum columns, lets just say A, but automatically look for the preceding Day summary and starting on the next row that isn't a weekly, monthly or annual summary.

Yea lets just start with the Day. I don't mind copying rows down for Daily, Monthly or Annual but then have it look for the relevant data to sum. Right now I have to copy the row then find/search to correct the rows to sum.

so sum column A and automatically look for the top row of the summary rows by "looking" for the "D", "WE", "M", or "A" in column M and using the next row.

 

[handysmurf]

https://drive.google.com/open?id=1SFDP05c1YV_oB-tNSEzQIYxTxXXp4jqs

OK so here is a massively simplified version of it....

So lets just start with ... Daily... for summary row A7 figure out that it needs to sum starting at row 2, for SR 20 figure out that it needs to sum starting at row 11 and so on for variations that may be there.

Hell this may not even be possible for all i know.

Edit: I should point out that column B will contain dates in between the summary rows.

 

[handysmurf]

Yea that is how it usually goes ... it makes sense on the first post but then i look back and it doesn't. Sorry.

 

[steve the fish]

So essentially you want to sum between the Ds?

 

[steve the fish]

With your simple example put a row in so the data starts at row 2 rather than row 1. Then in C2 put this formula and drag it down:

=IF(OR(B2<>"D",SUM($A$1:A1)=0),"",SUM($A$2:A2)-SUM($C$1:C1))