countif or sumproduct for this problem

Brass Contributor

i put the info in the sheet. im trying to add multiple criteria matches but im getting lost. any help is greatly appreciated!

 

Book1.xlsx

8 Replies

@rangelsammon 

 

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

 

DexterG_III_0-1663106605621.png

 

Thank you for the reply Dexter! can you save in the worksheet? I tried saving but i cant get it to calculate to see if it works

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

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

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

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

@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

Dexter! THANK YOU for your assistance! i really have to absorb this formula because there is plenty of functions i have never used. im going to play with it more. i think the end all requires a helper column. BUT your dedication to assist really means alot.