Excel arrays going down rows then across columns

Situation:
I am trying to create an array which is reasonable complex as follows:
I have a calendar which is setup with the staff listed across the columns
For each of the staff there are several rows below to enter their competencies which from a list of more than a dozen and each staff can have different competencies
Under the above is the calendar where each day is a row
For each day we enter if the staff are working days, night, extra days, extra nights or it is blank when they are not in.

What I want:
What I want to do is calculate for each day is calculate for each competency the number of staff who are in on days (including extra days) and do the same for nights (including extra nights) so that we can identify deficiencies in competencies i.e. manning.

Issues:
I tried this using excel arrays, but excel works through rows then columns and doesn’t provide the result I desire. I want it to work down rows then along the columns.
Is there a way of doing this with arrays?

Temporary solution:
I have created a formula to get about this; however, it is a very large formula calculates through the rows for each column and has the major set back that it doesn’t allow staff to be added or moved. It also increases the file size by a lot which is causing an issue on SharePoint.
The problem with the temporary solution is that the numbers of staff are increasing and each formula competency required 4000 characters. I have competencies 18 on each row * 365 days, this is then repeated for three years and will only increase. This is excluding the overtime (extra days or extra nights) which will double the formulas again!!!
Getting an array to work would be a major reduction in this and make my life so much easier!!!
If you know of a solution let me know. I am trying to refrain from VBA at the moment, if there is no other solution then this will have to be the go but I’m concerned about the processing demand to go calculate the code for the 18 cells *365 for each change.

Jon

If you post a workbook with sample data and the results you expect it will be much easier to help you.

{=SUM(($E$12:$CQ$18="I")*($E$20:$CQ$20="D"))}
Try this array formula (Ctrl + Shift + Enter)

Good one nflsales!
I took the liberty to play around with some more formulas in your sheet. You definitely need to get acquainted with array formulas.
There is more potential for improvements in that sheet.

OR and AND does not work in array formulas, it will just make a single OR or AND of the whole array (like SUM). Instead of OR you can use +. Instead of AND you can use * (which is what nflsales did).

In DH20 try this array formula:

Formula:

Please Login or Register  to view this content.