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
)
)
SergeiBaklan
Jul 14, 2021MVP
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
Jul 14, 2021Copper Contributor
thank you to everyone!! This has been a huge help!!