find the last row with data

[Guest]

I have an Inventorytab called INV1. I dump raw data in there. Some months
it will contain just 4,000 lines of data or up to 51,000 lines of data. My
second tab is a Calculator tab and it looks at data in the INV1 tab and does
a summary.

=COUNT(IF(Inv1!$A$2:$A$65536=A3,IF(Inv1!$C$2:$C$65536=B3,IF(Inv1!$E$2:$E$65536=C3,0)))) ....(this is an array)

Is there a way of putting something in the above formula to look at just the
rows with data?

The way I have my formula now means excel looks at row 2 to 65536 even
though a lot of rows are blank

Thanks

 

[T. Valko]

You can create a dynamic range.

Goto Insert>Name>Define
Name: rng
Refers to:

=OFFSET(Inv1!$A$2:$E$2,,,COUNTA(Inv1!$A$2:$A$65536))

Then, your formula becomes (still an array):

=COUNT(IF(INDEX(rng,,1)=A3,IF(INDEX(rng,,3)=B3,IF(INDEX(rng,,5)=C3,0))))

Or (still an array):

=SUM((INDEX(rng,,1)=A3)*(INDEX(rng,,3)=B3)*(INDEX(rng,,5)=C3))