Should it be Sumproduct with Offset?

[George J]

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?

 

[gaz_chops]

Maybe use sumifs with an offset -

=SUMIFS(G2:G25,G7:G30,"Agreed")

[TABLE="width: 174"]
<colgroup><col width="87" span="2" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87, align: right"]55[/TD]
[TD="width: 87, align: right"]154[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agreed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agreed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agreed[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

=SUMIF(Data!E6:E3000,"Agreed",Data!E1:E2995)

M.

 

[George J]

You Sir (Gaz) are a sneaky genius!!!

Seems to work for the Agreed - just need to try the the Not Removed now.

Many many thanks.


Both of you had the same great idea. I was too busy thinking how to look up and offset I didn't think about the various summing options.

thank you!

 

[gaz_chops]

You're welcome, works the same with other columns, just keep the sum range offset.