Sep 13 2022 11:37 AM
i put the info in the sheet. im trying to add multiple criteria matches but im getting lost. any help is greatly appreciated!
Sep 13 2022 03:05 PM
Is this what you were expecting?
If so, here is the formula I used in the Conditional Eval column. So if you want to calculate a sum outside the table you can simply sum the Conditional Eval column if that works.
=IF(OFFSET([@safety],2,0)<>"yes",0,COUNTIFS([player],[@player],[match],[@match],[ball in hand],"yes",[win],"yes",[inning],[@inning]))
Sep 13 2022 03:52 PM
Sep 13 2022 04:04 PM
@rangelsammon Sorry, I made the changes in an offline copy but just transferred to your online file and saved.
Sep 13 2022 09:52 PM
Sep 14 2022 11:27 AM
@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.
Sep 14 2022 11:41 AM
Sep 14 2022 08:31 PM
@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
Sep 15 2022 10:20 AM