There is no good reason for invoking a SumProduct formula when you have
to consider a single condition/criterion...

=SUMIF(INDEX(Sheet1!$A$1:$A$65536,NetIncomeRec+1): $A$65536,
TRIM($A1),
INDEX(Sheet1!$B$1:$B$65536,NetIncomeRec+1):$B$6553 6)

ExcelQuestion wrote:
Hello,
I have what I needed now. Moving forward, I'd also like to sum the
bottom half...everything else after "Net Income" through to the last
row. How do you do it? Macropod, in the earlier post, has the
OFFSET($B$1,COUNTA(A:A)-1,0
but I do have spaces in between so I couldn't fully utilize this
command. Any ideas?

Thanks,
Ricky



Aladin Akyurek Wrote:

Define NetIncomeRec as referring to:

=MATCH("Net Income",Sheet1!$B$1:$B$65536,0)

Then invoke a SumIf formula:

=SUMIF(Sheet1!$A$1:INDEX(Sheet1!$A$1:$A$65536,Ne tIncomeRec),
TRIM($A1),
Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncome Rec))

ExcelQuestion wrote:

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