Range of values meeting a range of criteria

Hi guys,

This is a bit of a complex one that's had me scratching my head for a while, as I'm not all too familiar with this side of excel.

4b8b64822b.png

As you can see, I've got 3 tables here. Firstly, a list of names and corresponding codes (any name could have any code). There can be multiple of the same name and same code in this table.
On the far right, we have a table of 3 categories of names.
Then in the middle, I've got a table that I want to show the final result. I'd ideally like for it to record every time anyone in a particular category corresponds to one of the codes.

For example, cell F2 would show the amount of times either Mary, Dale or Albert corresponded with a code 1 in the left hand table. Cell K3 would show the amount of times James, Alfred or Lisa corresponded with a code 6 in the left hand table.

Bear in mind, I may be working with much larger, and changing sets of data, so the formula will probably need to work when copied across the rest of the table, and couldn't just contain ("Mary","Dale","Albert"), for example.

Help is greatly appreciated!

Alex

Originally Posted by shukla.ankur281190

Try

F2

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across .

Fantastic! That seems to have worked perfectly, thanks so much!

Originally Posted by shukla.ankur281190

Happy to help and thank you for feedback and reputation

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi mate,

I've just come across a problem when doing some more tweaking. Firstly, when using larger and variable ranges, the formula (seemingly randomly) will match blank areas in the right hand column with blank areas in the left hand column (in the actual version of this that I'm working on, the left hand column tends to have some blanks). This, will hence add these to the total, which I do not want.

Secondly, I've noticed that the formula actually does not account for if a certain combination happens more than once, for example, if i had William and 10 occur twice or more in the left column, it would only count that particular occurrence once.

Do you have any work around for 1 or these two? I may have found a relatively dirty way of solving the first problem, but the second problem is a bit more pressing for me.

Much appreciated,

Alex