Counting items by attributes

[mpx300]

Hi,
I've got some problems to solve one case, to be honest I'm struggling with this issue since few days and have no more ideas, maybee because of the fact that I'm excel beginer.

Anyway, maybe somebody will have any ideas about that.

Problem description:
I've got staff list. Each particular person has some priviliges (marked from A to J) or no priviliges. There is no any rule how many priviliges one person may have maximally or minimally, so it means that e.g. Person AA may have 4 different priviliges, person BD 2 priviliges, person CC no priviliges.

I would like to create a calculator which will show defficiencies or surpluses of personnel basing on staff list analyze. The problem is that there are 89 persons availible and 9 types of priviliges. How to count defficiencis or surpluses in this case? Especially when e.g. person AA who has priviliges A,B,C,F can be used only to cover demand for one of privilige he has, for example A and I cannot use this person to cover demand for B,C,F.

I tried to set some conditions in order to get an optimum solution, however it's not so easy, that's why I decided to open a new topic. I think that setting the priorites for priviliges types is a must, otherwise there will be few solutions.

If anybody has any idea how to solve my problem or can give me any tips, I would like to thank You in advance. Maybe Solver mabye some macros, I don't know.

I'm attaching simplified sheet showing the problem.
Unfortunately, I'm not a native speaker, however I hope that I described my problem in understandable way.
Brgds, Peter

[FDibbins]

Im not exactly sure how you intend to determine surplus/deffic, but this, in C2, copied down, will count how many in each category....
=COUNTA(OFFSET('Personnel and priviliges'!$C$2,1,MATCH(Calculator!A2,'Personnel and priviliges'!$C$2:$M$2,0),91,1))

[mpx300]

Im not exactly sure how you intend to determine surplus/deffic, but this, in C2, copied down, will count how many in each category....
=COUNTA(OFFSET('Personnel and priviliges'!$C$2,1,MATCH(Calculator!A2,'Personnel and priviliges'!$C$2:$M$2,0),91,1))

It's not what I would like to achive. The thing is that as written down in first post, there is an amount of persons (in this case 89) who have some priviliges. I've got some demands regarding amount of people required for particular priviliges (in this case A-48, C-25, H-5, No priviliges - 43). When You summarize demand values (121 persons required in this case), there is an evidence of some deficiencies (-32). There is a problem how to calculate the data in order to distribute deficiency between priviliges categories in optimum. I think that there are many solutions, so each privilige type must have priority. Let's say that Cat. A is prio 1, cat C - prio 2, cat. H - prio 3, rest does not require any prioritizing because there is no dmenad. No priviliges cat. is the simple substraction.

I tried to solve this problem by creating conditions, however there are a lot of them and I belive that it must be simpler solution.

[FDibbins]

OK, so if you were doing this by hand, what would you do, and what would an answer look like?