+ Reply to Thread
Results 1 to 10 of 10

SumProduct Question

  1. #1
    Jasmine
    Guest

    SumProduct Question

    Is it possible to use the Sumproduct formula to search for a particular word
    and grab the amount in the next column, but 1 row down? For example, I would
    search for region 1, but want to grab the figure in the total loans column
    next to Consumer.

    Region Loan Type Total Loans
    --------- ------------ --------------
    Region 1 Commercial 150000
    Consumer 75000
    Region 2 Commercial 90000
    Consumer 145000

    If this is possible, how would the formula be written? Thank you so much!

  2. #2
    Forum Contributor
    Join Date
    11-29-2005
    Posts
    142
    Try:

    =Index(B1:B10,Match(X1,A1:A10,0)+1)

    where B1:B10 is the range containing your return values
    A1:A10 contain the lookup matches

    X1 is the value to match (i.e. Region 1).

  3. #3
    Ardus Petus
    Guest

    Re: SumProduct Question

    MATCH returns a row position.
    SUMPRODUCT doesn't.

    =INDEX(C2:C5,MATCH("Region 1",A2:A5,0)+1)

    HTH
    --
    AP

    "Jasmine" <[email protected]> a écrit dans le message de
    news: [email protected]...
    > Is it possible to use the Sumproduct formula to search for a particular
    > word
    > and grab the amount in the next column, but 1 row down? For example, I
    > would
    > search for region 1, but want to grab the figure in the total loans column
    > next to Consumer.
    >
    > Region Loan Type Total Loans
    > --------- ------------ --------------
    > Region 1 Commercial 150000
    > Consumer 75000
    > Region 2 Commercial 90000
    > Consumer 145000
    >
    > If this is possible, how would the formula be written? Thank you so much!




  4. #4
    Toshiba
    Guest

    RE: SumProduct Question

    It can be done with VLook up. I have used it in the past and it worked but
    don't recall any longer. One thing I know is you will need to make sure that
    your left most column will have to have the exact same text in it in order to
    do the look up. Sorry I don't have more info.
    --
    Toshiba


    "Jasmine" wrote:

    > Is it possible to use the Sumproduct formula to search for a particular word
    > and grab the amount in the next column, but 1 row down? For example, I would
    > search for region 1, but want to grab the figure in the total loans column
    > next to Consumer.
    >
    > Region Loan Type Total Loans
    > --------- ------------ --------------
    > Region 1 Commercial 150000
    > Consumer 75000
    > Region 2 Commercial 90000
    > Consumer 145000
    >
    > If this is possible, how would the formula be written? Thank you so much!


  5. #5
    Ron Coderre
    Guest

    RE: SumProduct Question

    Try something like this:

    With your sample data in A1:C6

    D1: =SUMIF(A1:A6,"Region 1",C2:C7)

    Notice the second reference is offset one row from the first reference.

    Is that something you can work with?

    ***********
    Regards,
    Ron

    XL2002, WinXP-Pro


    "Jasmine" wrote:

    > Is it possible to use the Sumproduct formula to search for a particular word
    > and grab the amount in the next column, but 1 row down? For example, I would
    > search for region 1, but want to grab the figure in the total loans column
    > next to Consumer.
    >
    > Region Loan Type Total Loans
    > --------- ------------ --------------
    > Region 1 Commercial 150000
    > Consumer 75000
    > Region 2 Commercial 90000
    > Consumer 145000
    >
    > If this is possible, how would the formula be written? Thank you so much!


  6. #6
    Ron Coderre
    Guest

    RE: SumProduct Question

    Perhaps this:
    Same concept as my 1st post (ranges offset by one row), but using LOOKUP

    =LOOKUP("REGION 1",A1:A6,C2:C7)

    Does that help?
    ***********
    Regards,
    Ron

    XL2002, WinXP-Pro


    "Ron Coderre" wrote:

    > Try something like this:
    >
    > With your sample data in A1:C6
    >
    > D1: =SUMIF(A1:A6,"Region 1",C2:C7)
    >
    > Notice the second reference is offset one row from the first reference.
    >
    > Is that something you can work with?
    >
    > ***********
    > Regards,
    > Ron
    >
    > XL2002, WinXP-Pro
    >
    >
    > "Jasmine" wrote:
    >
    > > Is it possible to use the Sumproduct formula to search for a particular word
    > > and grab the amount in the next column, but 1 row down? For example, I would
    > > search for region 1, but want to grab the figure in the total loans column
    > > next to Consumer.
    > >
    > > Region Loan Type Total Loans
    > > --------- ------------ --------------
    > > Region 1 Commercial 150000
    > > Consumer 75000
    > > Region 2 Commercial 90000
    > > Consumer 145000
    > >
    > > If this is possible, how would the formula be written? Thank you so much!


  7. #7
    Jasmine
    Guest

    Re: SumProduct Question

    I am getting a #N/A in the cell. Here is my formula.

    =INDEX('C:\Trend Cards\Past Due Reports\[April Past
    Due.xls]Sheet1'!C3:C10,MATCH("LowCountry - Murray",A3:A10,0)+2)

    "Vito" wrote:

    >
    > Try:
    >
    > =Index(B1:B10,Match(X1,A1:A10,0)+1)
    >
    > where B1:B10 is the range containing your return values
    > A1:A10 contain the lookup matches
    >
    > X1 is the value to match (i.e. Region 1).
    >
    >
    > --
    > Vito
    > ------------------------------------------------------------------------
    > Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
    > View this thread: http://www.excelforum.com/showthread...hreadid=539921
    >
    >


  8. #8
    Jasmine
    Guest

    Re: SumProduct Question

    Got it figured out. I needed to reference the worksheet I was pulling it from
    in the Match part of the formula. Thank you for the help!

    "Vito" wrote:

    >
    > Try:
    >
    > =Index(B1:B10,Match(X1,A1:A10,0)+1)
    >
    > where B1:B10 is the range containing your return values
    > A1:A10 contain the lookup matches
    >
    > X1 is the value to match (i.e. Region 1).
    >
    >
    > --
    > Vito
    > ------------------------------------------------------------------------
    > Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
    > View this thread: http://www.excelforum.com/showthread...hreadid=539921
    >
    >


  9. #9
    Forum Contributor
    Join Date
    11-29-2005
    Posts
    142
    I think you need to reference the other workbook in the Match() part of the formula too:

    =INDEX('C:\Trend Cards\Past Due Reports\[April Past
    Due.xls]Sheet1'!C3:C10,MATCH("LowCountry - Murray",'C:\Trend Cards\Past Due Reports\[April Past
    Due.xls]Sheet1'!A3:A10,0)+2)

    EDIT:

    I see you have figured it out at the same time as I posted the same solution. Great! and you're welcome

  10. #10
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Jasmine,

    Using your sample data in A1:C5,

    =SUMPRODUCT((A1:A5="Region 1")*(OFFSET(C1:C5,1,0)))

    This returned 75000 for Region 1 and 145000 for Region 2.

    Does that help?

    Steve

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1