Sumproduct (Range unknown, needs Search)

[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

 

[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]...

 

[Peo Sjoblom]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[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

 

[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))

 

[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

 

[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)

 

[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

 

[ExcelQuestion]

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

Thanks again,
Ricky