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....

 

[handysmurf]

Sum column A in cells A7,20,24,31.

 

[handysmurf]

OK I made some changes so maybe it will be easier to understand.

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

I copied the formulas to another column and remarked them so you can see what they do. I just want it to find the first row without me having to find and replace the numbers in the first cell reference manually.

 

[steve the fish]

This will work for your example:

=SUMPRODUCT(--(ROW($A$1:A6)>LOOKUP(2,1/($C$1:C6="D"),ROW($C$1:C6))),--(ISNUMBER($B$1:B6)),$A$1:A6)

 

[handysmurf]

OK I'm off to work for the weekend ... will try when I return on Tuesday

Thanks and I will let you know.