Sumproduct (Range unknown, needs Search)

E

ExcelQuestion

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 t
expand or compress depending on where the last row of "Net Income" i
situated. For example, if "Net Income" is on row 90; then, the en
range should be updated automatically to be $A$1:$A$90 so it doesn'
pick up totals past the "Net Income" rows.

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

Thanks,
Rick
 
M

macropod

hi Ricky,

You could try:
=SUMPRODUCT(--(Sheet1!$A$1:OFFSET($A$1,COUNTA(A:A)-1,0)=TRIM($A1)),(Sheet1!B
$1:OFFSET($B$1,COUNTA(A:A)-1,0)))
but this will only be reliable if all rows down to the last row in column A
are populated.

Cheers


"ExcelQuestion" <[email protected]>
wrote in message
news:[email protected]...
 
E

ExcelQuestion

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
 
A

Aladin Akyurek

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,NetIncomeRec),
TRIM($A1),
Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRec))
 
E

ExcelQuestion

Hello,
I have what I needed now. Moving forward, I'd also like to sum th
bottom half...everything else after "Net Income" through to the las
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 thi
command. Any ideas?

Thanks,
Ricky
 
A

Aladin Akyurek

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$65536)
 
B

Biff

Try this:

=SUMIF(INDEX(Sheet1!A:A,MATCH("Net
Income",Sheet1!A:A,0)):Sheet1!A65536,TRIM(A1),INDEX(Sheet1!B:B,MATCH("Net
Income",Sheet1!A:A,0)):Sheet1!B65536)

Biff

"ExcelQuestion" <[email protected]>
wrote in message
 
E

ExcelQuestion

Thanks Biff and Aladin,
This is exactly what I'm looking for. Both formulas are very nicely
done.

Thanks again,
Ricky
 
Top