Row function

  • Thread starter Thread starter goodfish
  • Start date Start date
G

goodfish

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.
 
=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.
Click to expand...

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