Results 1 to 2 of 2

Thread: SUMPRODUCT / INDEX MATCH Question
Thanks Thanks: 0 Likes Likes: 0

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

    Default SUMPRODUCT / INDEX MATCH Question

    Hello,

    I'm trying to use a sumproduct formula to pull in data from a table with 3 criteria, but I need one criteria to reference a table. Here's an example of what I'm trying to do.

    I'm trying to pull how many apples Chris sold in each state. Sheet 1

    New York Pennsylvania Florida
    Chris Apples 13 14 13

    Here is my data table, it is by city not state. Call this sheet 2

    Rep Product Buffalo Syracuse Harrisburg Scranton Miami Tampa Bay
    Chris Apples 5 2 3 4 0 4
    Kevin Bananas 6 0 1 4 2 8
    Chris Oranges 4 5 1 2 7 3
    Ted Apples 6 6 4 3 4 4
    Mike Oranges 0 1 1 6 7 8
    Chris Apples 4 2 4 3 4 5

    Here is the table I need then to reference the city to each state. Table

    New York Pennsylvania Florida
    Syracuse Harrisburg Miami
    Buffalo Scranton Tampa Bay


    I can do a sumproduct formula like this =SUMPRODUCT((Sheet2!B2:H7)*(Sheet2!A1:A7=Sheet1!A2)*(Sheet2!B1:B7=Sheet1!B2)*(Sheet2!A1:H1=Sheet1!C3),(C3 being a city not a state) that I could reference a city to work, but I can't seem to get it to reference the state table to pull in a city at once.

    Any ideas, I've tried doing an index match as the third condition referencing the state table but have had no luck.

    Thanks!

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,525
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMPRODUCT / INDEX MATCH Question

    Since this formula does not reference the states on sheet 1 you will need make sure it refrences the correct cities on sheet 2. If they are listed in the same order you can copy across.

    ABCDE
    1New YorkPennsylvaniaFlorida
    2ChrisApples131413
    3Kevinbananas6510

    Sheet1



    Array Formulas
    CellFormula
    C2{=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!A$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!A$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0)>0,1,0))}
    D2{=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!B$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!B$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0)>0,1,0))}
    E2{=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!C$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!C$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0)>0,1,0))}
    C3{=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!A$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!A$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0)>0,1,0))}
    D3{=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!B$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!B$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0)>0,1,0))}
    E3{=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!C$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!C$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0)>0,1,0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    ABCDEFGH
    1RepProductBuffaloSyracuseHarrisburgScrantonMiamiTampa Bay
    2ChrisApples523404
    3KevinBananas601428
    4ChrisOranges451273
    5TedApples664344
    6MikeOranges011678
    7ChrisApples424345
    8
    9
    10
    11New YorkPennsylvaniaFlorida
    12SyracuseHarrisburgMiami
    13BuffaloScrantonTampa Bay

    Sheet2



    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

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
  •