Results 1 to 4 of 4

Thread: SUMIF with INDEX MATCH Issue

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

    Default SUMIF with INDEX MATCH Issue

    I have the following data:

    A B C D E
    1 Team 1 1 Team 2 2
    2 Team 3 2 Team 4 1
    3 Team 1 1 Team 3 2
    4 Team 4 1 Team 1 2
    5 Team 2 2 Team 1 1
    6
    7 Team 1-Wins Team 1-Losses

    Whenever a team wins, a 1 is entered manually into Column B or Column D. Whenever a team loses, a 2 is entered into Column B or Column D. I'm trying to create a formula that will give me Team 1 wins in B7, and Team 1 losses in D7. I'm guessing that a SUMIF would cover the task of totaling 1's or 2's, but whenever I try to couple it with a INDEX MATCH in order to sum just Team 1 information, I can only get it to read the first instance of Team 1. The final formula should read the 1's in B1, B3, and D5 to give Team 1 3 wins in B7, and read the 2's in D4 to give Team 1 1 loss in D7. Is there a way to accomplish this combination? Any help appreciated greatly!

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUMIF with INDEX MATCH Issue

    Hi, something like this maybe:

    Excel 2013/2016
    ABCD
    1Team 11Team 22
    2Team 32Team 41
    3Team 11Team 32
    4Team 41Team 12
    5Team 22Team 11
    6
    7Team 1-Wins3Team 1-Losses1

    Sheet1



    Worksheet Formulas
    CellFormula
    B7=COUNTIFS(A1:A5,"Team 1",B1:B5,1)+COUNTIFS(C1:C5,"Team 1",D1:D5,1)
    D7=COUNTIFS(A1:A5,"Team 1",B1:B5,2)+COUNTIFS(C1:C5,"Team 1",D1:D5,2)

    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,931
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with INDEX MATCH Issue

    Or,

    [B7] =COUNTIFS(A1:C5,"Team 1",B1:D5,1)

    [D7] =COUNTIFS(A1:C5,"Team 1",B1:D5,2)

    Regards
    Last edited by bosco_yip; Jun 18th, 2019 at 07:23 AM.

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

    Default Re: SUMIF with INDEX MATCH Issue

    That is awesome. Thank you both!

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
  •