Sumifs problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2544587%22%20slang%3D%22en-US%22%3ESumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2544587%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20data%20table%20full%20of%20information%20on%20certain%20people.%20I%20would%20like%20to%20be%20able%20to%20look%20up%20the%20date%20of%20of%20a%20name%20and%20a%20certain%20lifts.%20For%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20look%20up%20athletes%20A%20bench.%20So%20I%20would%20have%20athlete%20A%20in%20the%20drop%20down%20list%2C%20and%20bench%2C%20then%20it%20provide%20the%20bench%20for%20athlete%20A.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20great.%3C%2FP%3E%3CP%3Ethank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2544587%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2545940%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2545940%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969628%22%20target%3D%22_blank%22%3E%40Dtripple40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20169px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F295672i69256922600A57A1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EA10%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%0A%20%20%20%20%20%20(NUMBERS%5B%5B%23Headers%5D%3B%5BBENCH%5D%3A%5BPULL%20UPS%5D%5D%20%3D%20A8)%0A%20%20%20%20*%20(NUMBERS%5BNAME%5D%20%3D%20A7)%0A%20%20%20%20*%20NUMBERS%5B%5BBENCH%5D%3A%5BPULL%20UPS%5D%5D%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20only%20have%201%20row%20of%20data%20per%20%5BNAME%5D%20in%20table%20NUMBERS%20the%20followings%20could%20do%20it%20as%20well%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EA11%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(NUMBERS%5B%5BBENCH%5D%3A%5BPULL%20UPS%5D%5D%2C%0A%20%20%20%20MATCH(A7%2CNUMBERS%5BNAME%5D%2C0)%2C%0A%20%20%20%20MATCH(A8%2CNUMBERS%5B%5B%23Headers%5D%2C%5BBENCH%5D%3A%5BPULL%20UPS%5D%5D%2C0)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EA12%3C%2FSTRONG%3E%20(Excel%20365%2FWeb%20only)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(%0A%20%20%20%20FILTER(NUMBERS%2C%20NUMBERS%5BNAME%5D%20%3D%20A7)%2C%0A%20%20%20%20NUMBERS%5B%23Headers%5D%20%3D%20A8%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20updated%20file%20is%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EEDIT%3A%3C%2FSTRONG%3E%20To%20be%20complete.%20If%20Excel%20365%2FWeb%20and%26nbsp%3Bmore%20than%201%20row%20of%20data%20per%20%5BNAME%5D%20in%20table%20NUMBERS%3A%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3DSUM(%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BFILTER(%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20FILTER(NUMBERS%2C%20NUMBERS%5BNAME%5D%20%3D%20A7)%2C%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20NUMBERS%5B%23Headers%5D%20%3D%20A8%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B)%3CBR%20%2F%3E)%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2546171%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInteresting%20use%20of%20FILTER.%26nbsp%3B%20I%20don't%20think%20I%20have%20ever%20used%20nested%20row%20and%20column%20filters.%26nbsp%3B%20I%20can%20see%20circumstances%20where%20it%20could%20be%20useful%20(e.g.%20return%20the%20average%20of%20the%20athletes%20top%203%20performances).%3C%2FP%3E%3CP%3ETo%20return%20a%20single%20value%20in%20365%2C%20I%20would%20tend%20to%20go%20for%20nested%20XLOOKUPs.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20athleteData%2C%20%20XLOOKUP(AthleteName%2C%20NUMBERS%5BNAME%5D%2C%20NUMBERS)%2C%0A%20%20%20XLOOKUP(Lift%2C%20NUMBERS%5B%23Headers%5D%2C%20athleteData))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%2C%20here%2C%20use%20LET%20to%20lay%20out%20the%20formula%20steps%20sequentially.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2546509%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYet%20another%20option%20(nested%20XLOOKUPs)%20for%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969628%22%20target%3D%22_blank%22%3E%40Dtripple40%3C%2FA%3E%26nbsp%3Bif%20365%20is%20used%20and%20only%201%20row%2Fathlete%3C%2FP%3E%3CP%3EThe%20case%20is%20a%20bit%20confusing%20as%20submitted%20as%20a%20SUMIF%20issue%20but%20the%20table%20shows%20only%201%20row%2Fathlete%2C%20hence%20why%20I%20suggested%20SUM(FILTER...)%20as%20a%20possible%20alternative%20to%20SUMPRODUCT%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2546965%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969628%22%20target%3D%22_blank%22%3E%40Dtripple40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20variant%20for%20the%20collection%3C%2FP%3E%0A%3CP%3E-%20Create%20on%20NUMBERS%20PivotTable%20in%20new%20sheet%20adding%20data%20to%20data%20models%3C%2FP%3E%0A%3CP%3E-%20Name%20to%20rows%2C%20Sum%20of%20each%20column%20to%20values%3C%2FP%3E%0A%3CP%3E-%20add%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCUBEVALUE(%22ThisWorkbookDataModel%22%2C%0A%20CUBEMEMBER(%22ThisWorkbookDataModel%22%2C%22%5BMeasures%5D.%5BSum%20of%20%22%20%20%26amp%3B%20%24A%248%20%26amp%3B%20%22%5D%22)%2C%0A%20CUBEMEMBER(%22ThisWorkbookDataModel%22%2C%22%5BNUMBERS%5D.%5BNAME%5D.%5B%22%20%20%20%26amp%3B%20%24A%247%20%26amp%3B%20%22%5D%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E-%20delete%20new%20sheet%20with%20PivotTable%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2548001%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2548001%22%20slang%3D%22en-US%22%3Ethank%20you%20to%20everyone!!%20This%20has%20been%20a%20huge%20help!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2558542%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558542%22%20slang%3D%22en-US%22%3ESo%20this%20has%20been%20a%20huge%20help.%20If%20i%20wanted%20to%20add%20a%20date%20and%20be%20able%20to%20look%20at%20a%20certain%20lift%20and%20athlete%20on%20a%20certain%20day%20how%20would%20I%20go%20about%20adding%20that%20in%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2558564%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558564%22%20slang%3D%22en-US%22%3Eand%20add%20like%20a%20position.%20Like%20Left%20back%20or%20Right%20wing%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2558590%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558590%22%20slang%3D%22en-US%22%3E%3CP%3EDifficult%20to%20say.%20You've%20got%20n%20proposals%20and%20we%20have%20no%20idea%20which%20one%20you%20implemented%3CBR%20%2F%3ECould%20you%20upload%20a%20sample%20with%20what%20you%20implemented%20%2B%20give%20a%20couple%20of%20examples%20of%20what%20you%20now%20expect%3F%3CBR%20%2F%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

8 Replies

Hello @Dtripple40 

 

Demo.png

 

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

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

@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

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

thank you to everyone!! This has been a huge help!!
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?
and add like a position. Like Left back or Right wing?

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