Forum Discussion
Dtripple40
Jul 13, 2021Copper Contributor
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...
- 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
)
)
Lorenzo
Jul 14, 2021Silver 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
)
)
- Dtripple40Jul 17, 2021Copper ContributorSo 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?
- Dtripple40Jul 17, 2021Copper Contributorand add like a position. Like Left back or Right wing?
- LorenzoJul 17, 2021Silver Contributor
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
- PeterBartholomew1Jul 14, 2021Silver Contributor
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.
- LorenzoJul 14, 2021Silver Contributor
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