Results 1 to 4 of 4

Thread: Sumif/sumproduct help!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumif/sumproduct help!

    I am struggling with creating a formula that works for the below scenario

    A B C
    1 Item Budget v1 Budget v2
    2 Item A 50 55
    3 Item B 60 65
    4 Item C 70 75
    5 Item D 80 85

    I have two sheets in one workbook. One with full budget data with in this scenario a Budget v1 and Budget v2 along with individual item names

    I want to set up a formula in a separate sheet to input the correct number based on two criteria, the specific item name and the specific budget number

    I can get SUM IF to work if there is only one criteria but not both. I have also tried SUMPRODUCT and INDEX MATCH scenarios but not successfully!

    I also want to avoid writing "Item A" as this formula will need to scan hundreds of lines of data - all individually named

    All help appreciated

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif/sumproduct help!

    Welcome to the lounge.

    Do you mean something like this:

    Code:
    =INDEX(A1:C5,MATCH("Item C",A1:A5,0),MATCH("Budget v2",A1:C1,0))
    Where you could put the item reference in a cell and the budget reference in another and change the specific quoted values above.

    The above formula would return 75.

    Alternatively:

    Code:
    =INDEX(Budget!A1:C5,MATCH("Item C",Budget!A1:A5,0),MATCH("Budget v2",Budget!A1:C1,0))
    Last edited by kweaver; Sep 9th, 2019 at 08:03 PM.

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif/sumproduct help!

    More general it would look like this:

    Excel 2010
    ABC
    1Item CBudget v275
    2Item ABudget v255
    3Item BBudget v160

    NewBudget



    Worksheet Formulas
    CellFormula
    C1=INDEX(Budget!$A$1:$C$500,MATCH(A1,Budget!$A$1:$A$500,0),MATCH(B1,Budget!$A$1:$C$1,0))


  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif/sumproduct help!

    Solved it, thanks so much!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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