Forum Discussion

rangelsammon's avatar
rangelsammon
Brass Contributor
Sep 13, 2022

countif or sumproduct for this problem

i put the info in the sheet. im trying to add multiple criteria matches but im getting lost. any help is greatly appreciated!   https://1drv.ms/x/s!AnFi6uGE1reki2F7ZOcOKhcx_EZj?e=JEaMwG
Formulas and Functions
DexterG_III's avatar
DexterG_III
Iron Contributor
Sep 13, 2022

rangelsammon Sorry, I made the changes in an offline copy but just transferred to your online file and saved.  

rangelsammon's avatar
rangelsammon
Brass Contributor
Sep 13, 2022
Dexter, thanks for taking time to help out 🙂 i was thinking the same it might need a helper column. do you know of any way to get the count of 2 without the sum of a helper column
  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 14, 2022

    rangelsammon Hello, I tried briefly last night without success.  I'm sure there's a way, perhaps using the ByRow combined with a Lamda function.  Do you have Excel 365?   That is a prerequisite for the newer dynamic array formulas.    

    • rangelsammon's avatar
      rangelsammon
      Brass Contributor
      Sep 14, 2022
      thank you again. i do have 365. i tried this formula but not working. but perhaps this formula better explains too what i want to do

      =SUMPRODUCT((data[match]=4)*(data[player]=Player1)*(data[win]="yes")*(data[ball in hand]="yes")*
      the next is where it goes wrong and where im struggling
      i need the inning where the yes happen in the above columns to match the inning. the end goal is to count how many times this overall happens
      i thought maybe do 2 index match formulas but now working
      (data[inning]=INDEX(data[inning],MATCH("yes",data[ball in hand])))*(data[inning]=INDEX(data[inning],MATCH("yes",data[win]))))
      • DexterG_III's avatar
        DexterG_III
        Iron Contributor
        Sep 14, 2022

        rangelsammon Okay I copied the original tab to a new one with "DextersEdits" in the title so I didn't accentally screw up your efforts.  I see you added a sequence of required calculations, which I leveraged to build the following formula.  You will see each of formula subcomponents split out & aligned to the sequence you've provided so it's easier to see what's going on.  

         

        =LET(
WinArray,UNIQUE(FILTER(data,(data[win]="Yes"))),
WinInning,BYROW(SORT(CHOOSECOLS(WinArray,1,3,4),{1,2,3}),LAMBDA(r,TEXTJOIN(",",,r))),
BallArray,FILTER(data,(data[ball in hand]="yes")),
BallInning,BYROW(SORT(CHOOSECOLS(BallArray,1,3,4),{1,2,3}),LAMBDA(r,TEXTJOIN(",",,r))),
SUM(ISNUMBER(SEARCH(WinInning,BallInning))*1)
)

         

        It works for the extremely limited dataset and the few adjustments I made.  I'm really unsure about all the various potential combinations that could exist in the data table -  so I'm not sure if it's scalable.  

         

        I didn't see the offset portion in the new description/sequence, is this still needed?  

         

        Check it out and let me know what you think.  

         

        Dexter