Sumifs with OR/AND array using cell references

I have a need to calculate the total number of calls for a given time frame on a given date and these calls come in on multiple skillsets. Right now I have a SUMIF statement for each skillset and I need to figure out how to combine these into 1 formula for resource management and my sanity.

Right now it looks like this:

Please Login or Register  to view this content.

There are 25 of these skillsets so you can see that 25 of these multiplied by 48 time intervals multiplied by 10 clients... the processing power is insane and excel bogs down.

I've tried a couple of things I have found on the internet for using arrays but I'm not having luck. Here is an example of what I need.

Please Login or Register  to view this content.

So I tried this:

Please Login or Register  to view this content.

I also tried wrapping each cell reference in " " but it's still not working.

Any help would be appreciated.

Book2.xlsm

Here is the file example. The reason I have it check an entire column is the raw data varies from 50,000 lines to 100,000+ lines. I guess I could still guestimate a maximum or something like that. I will try the sumproduct example as well.

Originally Posted by Tony Valko

Try something like this...

Make a list of the skillset variables. Let's assume that list is in the range X1:X25.

Then the formula becomes:

=SUMPRODUCT(--ISNUMBER(MATCH(Skillset!A2:A10,X1:X25,0)),--(Date!C2:C10=CellRef),--(Time!B2:B10=Cellref),Calls!D2:D10)

You should avoid using entire columns as range references in array formulas and the SUMPRODUCT function. Use smaller specific ranges.

This is actually working quite well. Thank you so much for your help!