Thanks Peo,
Works like a charm. Exactly what I'm looking for.

Thanks Macropod also. I went with Peo's solution as I do have contents
below the "Net Income" row.

Thanks to both once again,
Ricky


Peo Sjoblom Wrote:
One way


=SUMPRODUCT(--(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("Net
Income",Sheet1!$A:$A,0)-1)=TRIM($A1)),(Sheet1!B$1:INDEX(Sheet1!$B:$B,MATCH ("Net
Income",Sheet1!$A:$A,0)-1)))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"ExcelQuestion"

wrote in message
news:ExcelQuestion.2778jm_1146605101.7257@excelfor um-nospam.com...

Hello,
My current formula down column B is
=SUMPRODUCT(--(Sheet1!$A$1:$A$100=TRIM($A1)),(Sheet1!B$1:B$100))

My data in Sheet1 changes regularly so I'd need the ending range to
expand or compress depending on where the last row of "Net Income"
is
situated. For example, if "Net Income" is on row 90; then, the end
range should be updated automatically to be $A$1:$A$90 so it doesn't
pick up totals past the "Net Income" rows.

How do you add a search function so that the $A$100 row changes
according to the new row wherever "Net Income" moves to?

Thanks,
Ricky


--
ExcelQuestion

------------------------------------------------------------------------
ExcelQuestion's Profile:
http://www.excelforum.com/member.php...o&userid=34059
View this thread:
http://www.excelforum.com/showthread...hreadid=538233



--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059
View this thread: http://www.excelforum.com/showthread...hreadid=538233