| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (Range unknown, needs Search)
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
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" 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
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,NetI ncomeRec), TRIM($A1), Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRe c)) 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
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,NetI ncomeRec), TRIM($A1), Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRe c)) 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
Try this:
=SUMIF(INDEX(Sheet1!A:A,MATCH("Net Income",Sheet1!A:A,0)):Sheet1!A65536,TRIM(A1),INDE X(Sheet1!B:B,MATCH("Net Income",Sheet1!A:A,0)):Sheet1!B65536) Biff "ExcelQuestion" wrote in message news:ExcelQuestion.277gez_1146615301.8824@excelfor um-nospam.com... 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,NetI ncomeRec), TRIM($A1), Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRe c)) 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct (Range unknown, needs Search)
Thanks Biff and Aladin, This is exactly what I'm looking for. Both formulas are very nicely done. Thanks again, Ricky Biff Wrote: Try this: =SUMIF(INDEX(Sheet1!A:A,MATCH("Net Income",Sheet1!A:A,0)):Sheet1!A65536,TRIM(A1),INDE X(Sheet1!B:B,MATCH("Net Income",Sheet1!A:A,0)):Sheet1!B65536) Biff "ExcelQuestion" wrote in message news:ExcelQuestion.277gez_1146615301.8824@excelfor um-nospam.com... 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,NetI ncomeRec), TRIM($A1), Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$65536,NetIncomeRe c)) 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 ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=538233 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use an index number in a search range | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| how do you search a range of cells... | Excel Discussion (Misc queries) | |||
| SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
| using sumproduct in a range of text fields? | Excel Worksheet Functions | |||
Linear Mode
