Forum Discussion

Dtripple40's avatar
Dtripple40
Copper Contributor
Jul 13, 2021

Sumifs problem

Hello,

 

I have a data table full of information on certain people. I would like to be able to look up the date of of a name and a certain lifts. For example

 

I want to be able to look up athletes A bench. So I would have athlete A in the drop down list, and bench, then it provide the bench for athlete A. 

 

Any help would be great.

thank you!

  • Hello Dtripple40 

     

     

    in A10

     

    =SUMPRODUCT(
          (NUMBERS[[#Headers];[BENCH]:[PULL UPS]] = A8)
        * (NUMBERS[NAME] = A7)
        * NUMBERS[[BENCH]:[PULL UPS]]
    )

     

     

    If you only have 1 row of data per [NAME] in table NUMBERS the followings could do it as well

    in A11

     

    =INDEX(NUMBERS[[BENCH]:[PULL UPS]],
        MATCH(A7,NUMBERS[NAME],0),
        MATCH(A8,NUMBERS[[#Headers],[BENCH]:[PULL UPS]],0)
    )

     

    in A12 (Excel 365/Web only)

     

    =FILTER(
        FILTER(NUMBERS, NUMBERS[NAME] = A7),
        NUMBERS[#Headers] = A8
    )

     

    Your updated file is attached

     

    EDIT: To be complete. If Excel 365/Web and more than 1 row of data per [NAME] in table NUMBERS:

    =SUM(
       FILTER(
          FILTER(NUMBERS, NUMBERS[NAME] = A7),
          NUMBERS[#Headers] = A8
       )
    )

  • Dtripple40 

    One more variant for the collection

    - Create on NUMBERS PivotTable in new sheet adding data to data models

    - Name to rows, Sum of each column to values

    - add formula

    =CUBEVALUE("ThisWorkbookDataModel",
     CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of "  & $A$8 & "]"),
     CUBEMEMBER("ThisWorkbookDataModel","[NUMBERS].[NAME].["   & $A$7 & "]"))

    - delete new sheet with PivotTable

     

    In attached.

    • Dtripple40's avatar
      Dtripple40
      Copper Contributor
      thank you to everyone!! This has been a huge help!!
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hello Dtripple40 

     

     

    in A10

     

    =SUMPRODUCT(
          (NUMBERS[[#Headers];[BENCH]:[PULL UPS]] = A8)
        * (NUMBERS[NAME] = A7)
        * NUMBERS[[BENCH]:[PULL UPS]]
    )

     

     

    If you only have 1 row of data per [NAME] in table NUMBERS the followings could do it as well

    in A11

     

    =INDEX(NUMBERS[[BENCH]:[PULL UPS]],
        MATCH(A7,NUMBERS[NAME],0),
        MATCH(A8,NUMBERS[[#Headers],[BENCH]:[PULL UPS]],0)
    )

     

    in A12 (Excel 365/Web only)

     

    =FILTER(
        FILTER(NUMBERS, NUMBERS[NAME] = A7),
        NUMBERS[#Headers] = A8
    )

     

    Your updated file is attached

     

    EDIT: To be complete. If Excel 365/Web and more than 1 row of data per [NAME] in table NUMBERS:

    =SUM(
       FILTER(
          FILTER(NUMBERS, NUMBERS[NAME] = A7),
          NUMBERS[#Headers] = A8
       )
    )

    • Dtripple40's avatar
      Dtripple40
      Copper Contributor
      So this has been a huge help. If i wanted to add a date and be able to look at a certain lift and athlete on a certain day how would I go about adding that in?
      • Dtripple40's avatar
        Dtripple40
        Copper Contributor
        and add like a position. Like Left back or Right wing?
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      Lorenzo 

      Interesting use of FILTER.  I don't think I have ever used nested row and column filters.  I can see circumstances where it could be useful (e.g. return the average of the athletes top 3 performances).

      To return a single value in 365, I would tend to go for nested XLOOKUPs.

      = LET(
         athleteData,  XLOOKUP(AthleteName, NUMBERS[NAME], NUMBERS),
         XLOOKUP(Lift, NUMBERS[#Headers], athleteData))

      or, here, use LET to lay out the formula steps sequentially. 

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        PeterBartholomew1 

        Yet another option (nested XLOOKUPs) for @Dtripple40 if 365 is used and only 1 row/athlete

        The case is a bit confusing as submitted as a SUMIF issue but the table shows only 1 row/athlete, hence why I suggested SUM(FILTER...) as a possible alternative to SUMPRODUCT

Resources