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