Returning the last row that has data in a specific column

[Blanchetdb]

I need a formula that will return the last row that has data within column E

example:
Column E Column F Column G Column H

Science	Bob	AS	NCR
	Fred	EG	Quebec
HR	Jane	CR	Ontario
	Susy	PM	Ontario
Operations	John	EG	West

I need the formula to return a count of 5

the formula is located on Sheet "Engine" A2 but the data in on Sheet "Staffing-Processes" E2:E

thank you

 

[Blanchetdb]

I need a formula that will return the last row that has data within column E

example:
Column E Column F Column G Column H

Science	Bob	AS	NCR
	Fred	EG	Quebec
HR	Jane	CR	Ontario
	Susy	PM	Ontario
Operations	John	EG	West

I need the formula to return a count of 5

the formula is located on Sheet "Engine" A2 but the data in on Sheet "Staffing-Processes" E2:E

thank you

Just found it

=SUMPRODUCT(MAX(('Staffing-Processes'!A2:E5000<>"")*ROW('Staffing-Processes'!E2:E5000)))

 

[Bosquedeguate]

OK ... a bit of clarificatiion is needed. It looks like your sample sheet has data in column E (i.e. Science, HR, and Operations).
It is easy to find the last row. But, what is this function supposed to return? the Last Row of data? the Last 5 rows of data? just the number 5 (and why 5?)
Please clarify.

 

[Peter_SSs]

Just found it

=SUMPRODUCT(MAX(('Staffing-Processes'!A2:E5000<>"")*ROW('Staffing-Processes'!E2:E5000)))

Whilst that will certainly work, SUMPRODUCT is a fairly inefficient function. Another option would be

=AGGREGATE(14,6,ROW('Staffing-Processes'!E1:E5000)/('Staffing-Processes'!E1:E5000<>""),1)

.. or if the entries in col E will always be text values you could also try

=LOOKUP("zzz",'Staffing-Processes'!E2:E5000,ROW('Staffing-Processes'!E2:E5000))