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

 

[Fluff]

Cross posted Simplify this formula containing sum(countis() function

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[pjmorris]

I know it looks weird!

The DATE range is a single column, but PROC columns cover 7 columns and I needed the DATE column to be replicated into a similar sized array. Multiplying one single sized array by another results in an array of the correct size. It then tests that new array against your date criteria.

Hope that makes sense.

 

[pjmorris]

the DATE array is multiplied by this simple array {1,1,1,1,1,1,1} which produces a 7 column wide array that has the DATE values spread across the 7 column width. This is then matched against the date criteria to produce an array of True/False values. This in turn is matched to the PROC columns and their criteria (the + between each test is effectively an OR function) to only select valid elements to be counted.

HTH

 

[gfdwer2]

Your formula doesn't include OT7 - should it?

NOPE IT DOES NOT INCLUDE OT 7

 

[gfdwer2]

Your formula doesn't include OT7 - should it?

I TYPED WRONGLY EARLIER IT SHOULD ONLY BE OT 1 - OT 6

 

[gfdwer2]

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

I TRIED YOUR SUGGESTION BY REPLACING D3:J23 WITH DATA!H3:Z100 BUT IT GIVES ME VALUE ERROR

 

[pjmorris]

it will if DATE does not have the same number of rows. The arrays in the sumproduct must be the same size.

 

[gfdwer2]

I WAS ABLE TO

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

I WAS ABLE TO SOLVE THIS PROBLEM BY LIMITING MY RANGES TO A MAXIMUM OF 5000

 

[RoryA]

You can simplify with a wildcard then:

=COUNTIFS(DATE,A5,PROC1,"OT *")+COUNTIFS(DATE,A5,PROC2,"OT *")+COUNTIFS(DATE,A5,PROC3,"OT *")+COUNTIFS(DATE,A5,PROC4,"OT *")+COUNTIFS(DATE,A5,PROC5,"OT *")+COUNTIFS(DATE,A5,PROC6,"OT *")+COUNTIFS(DATE,A5,PROC7,"OT *")

 

[gfdwer2]

You can simplify with a wildcard then:

=COUNTIFS(DATE,A5,PROC1,"OT *")+COUNTIFS(DATE,A5,PROC2,"OT *")+COUNTIFS(DATE,A5,PROC3,"OT *")+COUNTIFS(DATE,A5,PROC4,"OT *")+COUNTIFS(DATE,A5,PROC5,"OT *")+COUNTIFS(DATE,A5,PROC6,"OT *")+COUNTIFS(DATE,A5,PROC7,"OT *")

THAT MAKES SENSE, I'LL TRY TO USE THAT AND UPDATE YOU