find the last row with data
 

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$65 536=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

find the last row with data
 

You can create a dynamic range.

Goto InsertNameDefine
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))


--
Biff
Microsoft Excel MVP


"Poppy" wrote in message
...
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$65 536=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