Count values with multiple OR criteria

[VisionSmart]

I trust everyone is safe and healthy?
Working on a Matrix for Gymnastics Judges.
I cannot work out the correct formula to record the allocations of Judges to Candidates. - summary resullts
The Outcome required is as per Allocation Tab in the attached example.
My suggested formula does not work – Maybe I am on the wrong track?
Would appreciate the correct formula placed in the Workbook and returned.
THankyou

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Glenn Kennedy]

Thanks for that:

=SUMPRODUCT(--((Draw_1!$B$11:$L$17=D$6)+(Draw_1!$C$11:$M$17=D$6)+(Draw_1!$D$11:$N$17=D$6))*(Draw_1!$E$11:$O$17=$A11))

seems to do what you want.

[VisionSmart]

Thankyou for Speedy response Glen.

[JohnTopley]

@Glenn,
I notice you frequently enter SUMPRODUCT as an array formula (or they appear so in 2010) ?????

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Glenn Kennedy]

JT... really?? Now I'm on O365, I enter nothing as an array (for myself). I suspect there's a "feature" at play here. I looked at the formula which I used... and the microsoft compatability checker DOES indeed tell me that it will be converted into an array formula in older Excel versions. Clearly that formula requires NO array entry. Maybe I need to read a bit more about the new (and very annoying) @ implicit intersection thing they've introduced in O365.

[VisionSmart]

Reputation message says I need to share points - but don't understand how to do this - sorry Glenn
I will hold on the Solved until I completely test on my live Workbook which is considerable size.

[Glenn Kennedy]

VisionSmart... if you're still here... can you CONFIRM

a) that the formula looks like an array formula when you open my file, and

b) that it works equally well when set with just Enter?

See John T's comment a post or two earlier.

[Glenn Kennedy]

It just means that the last person you gave some to was me... it's a measure to stop rep-swapping cartels. Someone else needs to benefit from your rep button...

[JohnTopley]

@Glenn: formula works OK with normal entry: I "suspected" it that it might be related to an OS365 "feature"!

[Glenn Kennedy]

JT... is it just me, or have you seen this with any other O365-users?

[VisionSmart]

Formula shows the Array symbols and works on 2020 OK thanks
How do I include J4, J5, J6 as I plan to include in the master Workbook?

[Glenn Kennedy]

I'm not sure how you want to include them. Repost a sample sheet showing a few expected answers.

[JohnTopley]

@Glenn, I have not noticed it with other OS365 Users but I will keep an eye out for this with the OS365 community.

[Glenn Kennedy]

OK. I'll have a guess at this, as it's night time in Oz... I have gone back to something much more like your original formula:

=SUMPRODUCT(COUNTIFS(Draw_1!$B$11:$L$17,D$6:D$7,Draw_1!$E$11:$O$17,$A11)+COUNTIFS(Draw_1!$C$11:$M$17,D$6:D$7,Draw_1!$E$11:$O$17,$A11)+COUNTIFS(Draw_1!$D$11:$N$17,D$6:D$7,Draw_1!$E$11:$O$17,$A11))

[VisionSmart]

That formula works terrific! - using Array process

Thankyou Glenn.