Sumifs problem

[Dtripple40]

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!

[#2 · Accepted solution]

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

[#3]

@L z. 

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. 

[#4]

@Peter Bartholomew 

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

[#5]

@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.

[#6]

thank you to everyone!! This has been a huge help!!

[#7]

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?

[#8]

and add like a position. Like Left back or Right wing?

[#9]

Difficult to say. You've got n proposals and we have no idea which one you implemented
Could you upload a sample with what you implemented + give a couple of examples of what you now expect?
Thanks