George J
Well-known Member
- Joined
- Feb 15, 2002
- Messages
- 959
I have a report over 2 sheets in a workbook.
A summary page and a data page.
This would be easier if I was allowed to write code for it, but as they will be sending it to clients, it must be formulas.
I am trying to put a subtotal on the summary page.
I am trying to lookup "Agreed" in column E of the Data page and wherever that is found, 5 rows above is a value I need, but I need to add all the Agreed offset 5 values together.
For another subtotal I need the lookup "Removed" in column E and "NOT" in column D and 5 rows above in column D, I need to total all of these values. There are around 500 entries on the Data sheet so goes over 2500 rows.
I initially tried sumproduct(--('Data'!E:E="Agreed")* indirect( row( and other variations, trying to subtract the number of rows. I also don't think offset is what I am looking for.
Has anyone done anything similar before?
A summary page and a data page.
This would be easier if I was allowed to write code for it, but as they will be sending it to clients, it must be formulas.
I am trying to put a subtotal on the summary page.
I am trying to lookup "Agreed" in column E of the Data page and wherever that is found, 5 rows above is a value I need, but I need to add all the Agreed offset 5 values together.
For another subtotal I need the lookup "Removed" in column E and "NOT" in column D and 5 rows above in column D, I need to total all of these values. There are around 500 entries on the Data sheet so goes over 2500 rows.
I initially tried sumproduct(--('Data'!E:E="Agreed")* indirect( row( and other variations, trying to subtract the number of rows. I also don't think offset is what I am looking for.
Has anyone done anything similar before?