Sumproduct (Range unknown, needs Search)

**ExcelQuestion** · 05-02-2006, 05:24 PM

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

macropod · 05-02-2006, 05:40 PM

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

Peo Sjoblom · 05-02-2006, 05:45 PM

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]...
>
> 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** · 05-02-2006, 06:16 PM

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

Originally Posted by 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]...
>
> 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
>

Aladin Akyurek · 05-02-2006, 07:35 PM

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

**ExcelQuestion** · 05-02-2006, 08:13 PM

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

Originally Posted by 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 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
>
>

Aladin Akyurek · 05-02-2006, 08:45 PM

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)

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

Biff · 05-02-2006, 08:50 PM

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
news:[email protected]...
>
> 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,NetIncomeRec),
>> TRIM($A1),
>> Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRec))
>>
>> 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
>> >
>> >
>
>
> --
> ExcelQuestion
> ------------------------------------------------------------------------
> ExcelQuestion's Profile:
> http://www.excelforum.com/member.php...o&userid=34059
> View this thread: http://www.excelforum.com/showthread...hreadid=538233
>

**ExcelQuestion** · 05-03-2006, 02:24 AM

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

Thanks again,
Ricky

Originally Posted by 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
news:[email protected]...
>
> 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,NetIncomeRec),
>> TRIM($A1),
>> Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRec))
>>
>> 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
>> >
>> >
>
>
> --
> ExcelQuestion
> ------------------------------------------------------------------------
> ExcelQuestion's Profile:
> http://www.excelforum.com/member.php...o&userid=34059
> View this thread: http://www.excelforum.com/showthread...hreadid=538233
>