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 athlet...
excel
  • Lorenzo's avatar
    Lorenzo
    Jul 14, 2021

    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
       )
    )

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 14, 2021

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
Jul 14, 2021

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