Hi All!
I am using the following formula to add totals on a sheet named Apps. & Invs.

=SUMPRODUCT(SUBTOTAL(9;OFFSET('Apps. & Invs.'!I$1;ROW('Apps. &
Invs.'!I$2:OFFSET('Apps. & Invs.'!I54;;))-1;))*('Apps. &
Invs.'!H$2:OFFSET('Apps. & Invs.'!H54;;)="ord."))

I entered the I54 and H54 references myself but in fact I would like these
to be the last cell on each column. Can someone help me correct this!
Also I've read the Row() explanations on excel help but cannot understand
what it equates to in this example...any explanations are very welcome!

Hello again!
Sorry I have read over my question and it is kind of difficult to interpret...
Basically i have found a formula (on the forum) to subtotal only filtered
items.
The formula is in a cell on a separate sheet to the filtered data.... which
means 2 things:
1) the formula looks a bit messy and is a bit hard to follow (this is a
simplified version)
=SUMPRODUCT(SUBTOTAL(9;OFFSET($A$1;ROW(A$2:$A54)-1;))*($B$2:$B54="ord."))

2) the formula needs to reference a dynamic range so Row(A$2:$A54) obviously
is not valid once data goes beyond A54.

Hope this has made the matter more simple to understand.
Any help very much appreciated.

"goodfish" wrote:

Hi All!
I am using the following formula to add totals on a sheet named Apps. & Invs.

=SUMPRODUCT(SUBTOTAL(9;OFFSET('Apps. & Invs.'!I$1;ROW('Apps. &
Invs.'!I$2:OFFSET('Apps. & Invs.'!I54;;))-1;))*('Apps. &
Invs.'!H$2:OFFSET('Apps. & Invs.'!H54;;)="ord."))

I entered the I54 and H54 references myself but in fact I would like these
to be the last cell on each column. Can someone help me correct this!
Also I've read the Row() explanations on excel help but cannot understand
what it equates to in this example...any explanations are very welcome!

=SUMPRODUCT(SUBTOTAL(9;OFFSET($A$1;ROW(A$2:$A54)-1;))*($B$2:$B54="ord."))
2) the formula needs to reference a
dynamic range so Row(A$2:$A54)
obviously is not valid once data goes beyond A54.

OK, so replace ROW(A$2:$A54) with the dynamic range, or, is that the part
you need help with? You'll also have to replace $B$2:$B54 with the dynamic
range.

Are there any empty/blank cells within A$2:$A54?

Basically, what your formula is doing is a SUMIF(B2:B54,"ord",A2:A54) on a
filtered range.

--
Biff
Microsoft Excel MVP


"goodfish" wrote in message
...
Hello again!
Sorry I have read over my question and it is kind of difficult to
interpret...
Basically i have found a formula (on the forum) to subtotal only filtered
items.
The formula is in a cell on a separate sheet to the filtered data....
which
means 2 things:
1) the formula looks a bit messy and is a bit hard to follow (this is a
simplified version)
=SUMPRODUCT(SUBTOTAL(9;OFFSET($A$1;ROW(A$2:$A54)-1;))*($B$2:$B54="ord."))

2) the formula needs to reference a dynamic range so Row(A$2:$A54)
obviously
is not valid once data goes beyond A54.

Hope this has made the matter more simple to understand.
Any help very much appreciated.

"goodfish" wrote:

Hi All!
I am using the following formula to add totals on a sheet named Apps. &
Invs.

=SUMPRODUCT(SUBTOTAL(9;OFFSET('Apps. & Invs.'!I$1;ROW('Apps. &
Invs.'!I$2:OFFSET('Apps. & Invs.'!I54;;))-1;))*('Apps. &
Invs.'!H$2:OFFSET('Apps. & Invs.'!H54;;)="ord."))

I entered the I54 and H54 references myself but in fact I would like
these
to be the last cell on each column. Can someone help me correct this!
Also I've read the Row() explanations on excel help but cannot understand
what it equates to in this example...any explanations are very welcome!