Simplify this formula containing sum(countifs() function

[gfdwer2]

I NEED THIS FORMULA TO BE SIMPLIFIED BECAUSE IT TAKES A LOT OF TIME FOR EXCEL TO COMPUTE IT

Simple background, I need to count all the cells containing "OT 1","OT 2","OT 3","OT 4","OT 5","OT 6" from 7 columns (PROC1-7) , but only for a specific date (represented by A5)

=SUM(COUNTIFS(DATE,A5,PROC1,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))+SUM(COUNTIFS(DATE,A5,PROC2,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))+SUM(COUNTIFS(DATE,A5,PROC3,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))+SUM(COUNTIFS(DATE,A5,PROC4,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))+SUM(COUNTIFS(DATE,A5,PROC5,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))+SUM(COUNTIFS(DATE,A5,PROC6,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))+SUM(COUNTIFS(DATE,A5,PROC7,{"OT 1","OT 2","OT 3","OT 4","OT 5","OT 6"}))

FROM THE FORMULA ABOVE:
1. DATE is a range selection which is Column A from different sheet (DATA)
2. Content of Cell A5 is the first criteria I am looking for in range DATE
3. PROC1 is a range selection which is Column H from different sheet (DATA)
PROC2 is a range selection which is Column K from different sheet (DATA)
PROC3 - PROC7 so on and so forth
4. "OT 1" - "OT 6" are the text I am looking for in range PROC1-PROC7

I have to separate EACH PROC (Column H, Column K, so on so forth) because excel does not allow me to use all the columns at once like H:Z

sample excel sheet can be found in this link: sample.1.xlsm

 

[AlanY]

you should limit your name ranges DATE, PROC1 etc close to the numbers expected, e.g. A1:A100.

atm, you're checking over 1 million rows for 10+ items each of the PROC

 

[pjmorris]

What other values might they contain other than OT 1 to OT 7 inclusive?

 

[RoryA]

Do you have any other values beginning with "OT" that you don't want included?

 

[gfdwer2]

you should limit your name ranges DATE, PROC1 etc close to the numbers expected, e.g. A1:A100.

atm, you're checking over 1 million rows for 10+ items each of the PROC

I GET YOUR POINT THERE

 

[gfdwer2]

Do you have any other values beginning with "OT" that you don't want included?

NOPE, I ONLY HAVE OT 1 - OT 7

 

[gfdwer2]

What other values might they contain other than OT 1 to OT 7 inclusive?

Only OT 1 to OT 7, no other value or text included in a cell with OT 1 to OT 7 since they are special codes.

 

[pjmorris]

ideally try this, but replace D3:J23 with a single range that spans Proc1-Proc7. I assume that there are as many rows in DATE as there are in PROC?

=SUMPRODUCT(--((DATE)*{1,1,1,1,1,1,1}=A5),--((D3:J23="OT1")+(D3:J23="OT2")+(D3:J23="OT3")+(D3:J23="OT4")+(D3:J23="OT5")+(D3:J23="OT6")))

PS, as far a I know there is no reason why you can't have a single named range covering all 7 columns, even if they are the entire column. Though as said previously its not clever to reference an entire column because of the size of the calculation involved.

HTH

 

[RoryA]

NOPE, I ONLY HAVE OT 1 - OT 7

Your formula doesn't include OT7 - should it?

 

[JimM]

@pjmorris Any chance you could explain what this part of your formula is doing

((DATE)*{1,1,1,1,1,1,1}=A5)