SUMPRODUCT with Multiple Criteria

[technik]

Hi

I've looked online for help but not having any joy with exactly my problem which I am now tackling a different way.

I am trying to match candidate names and then also count how many of them (ie. matched candidates only) have a score of e.g. >1

I can match the candidates but am struggling to do a count of how many have a score of e.g. >1 because I am comparing 3 columns.

So I need to match the WHARFEDALE candidates with the AIREDALE candidates first, and then I need to count how many of the WHARFEDALE candidates have a score >1 AND/OR how many AIREDALE candidates have a score >1. The AIREDALE candiates get 2 'tries' so a score >1 in either try will add to the counter. I DON'T need a score >1 in all three columns D, I, K. Only a score >1 in D + score >1 in EITHER column I, K. Also don't need a repeat count if score is >1 in all three columns D,I,K.

Could somebody let me know if there is an easy way of doing this in Excel .... my file is attached.

TIA

[Richard Buttrey]

Hi

Would you manually add the results you expect, presumably in N4:N6 and then explain how you have calculated the results, referencing all the cells which have contributed to the results.

[technik]

Hi Richard

Thanks for the reply.

If I've understood you correctly the initial data in columns B,G,C,H,J is manually added yes. I just then need to do a match of names and the scores >1 as explained in Post1.

[technik]

If this would be easier to do in VBA I'd appreciate it if someone could let me know.

Not sure an EXCEL function can handle all that I need here?

[Richard Buttrey]

Hi Richard

Thanks for the reply.

If I've understood you correctly the initial data in columns B,G,C,H,J is manually added yes. I just then need to do a match of names and the scores >1 as explained in Post1.

Sorry, but it's because I don't understand from your post #1 what results you want in N4:N6 that I asked for specific clarification.
Please upload the workbook, manually add the results to N4:N6 and explain by referencing the specific cells that contribute to the results.

Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out". We're usually quite good but not prescient.

Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

[Glenn Kennedy]

Like Richard, I don't really follow what you want... but here's a wild guess.

If it's wrong (and it probably will be), please explain FULLY how results are meant to be calculated...

[technik]

Hi Richard / Glenn

Apologies - I think I am probably guilty of assuming that the problem is (or I think it is) relatively straight forward for guys like yourself but clearly I haven't explained it well enough.

All I am trying to do is do a count of matching candidates that have a score of >1 (using the helper 'score' columns) in BOTH groups.

So for e,g,

Alberta Wilson has a score of 3 in WHARFEDALE and has a score of 2 and 3.66 for AIREDALE (because AIREDALE get two tries). So she would count as she has a score of >1 in WHARFEDALE and a score >1 in AIREDALE on EITHER tries (only needs >1 in AIREDALE on either of the tries i.e. 1st try or 2nd try - doesn't have to be >1 in both tries for AIREDALE)

However if you look at Emma Maldonado .... she has a score of 1 for WHARFEDALE and a score of 2.66 (1st try) and 3 (2nd try) for AIREDALE. So she WOULDN'T count as she didn't score >1 in WHARFEDALE.

And so on for all matching candidates.

I hope it's a bit clearer. I've uploaded the sheet again (as some repeat names crept in the WHARFEDALE group).

Sorry Glenn that doesn't work. There are only 27 candidates in WHAREDALE group so the MAX matching candidates could only ever be 27 therefore the max count would be 27. But your formula is returning 40 but appreciate that I probably didn't do a good job of explaining things initially.

[Glenn Kennedy]

Aha. I assumed that you wanted a score for everyone, irrespective of which list they were on. Back to the drawing board...

[Richard Buttrey]

Does the attached help

I've added three helper columns so that you can better see the idea, but then put them all together in a composite helper column so that you can dispense with the three working columns.

[technik]

Hi Richard ... think that does it, thanks a lot. Didn't think it would need so many helper columns ! .... so I working on a VBA solution but think this does it.

Thanks again!

[Richard Buttrey]

Maybe you missed the point I was making.

You can delete the three helper columns leaving just the last composite helper column.

The three columns were just intermediate working steps. The composite helper column incorporates all three formulae