COUNTIFS

[kparadise]

Hello,

I have a pretty basic/standard COUNTIFS formula which is working great. However, I am tying to add one more criteria to this formula, and when I do, only the first cell works. When I try to paste the formula down, it turns all too zero. I know that is not appropriate just by knowing the data. Here is my current formula.

=COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$K:$K, "*"&Q8&"*", 'Testing 2020'!$G:$G, "Completed")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$K:$K, "*"&Q8&"*", 'Testing 2020'!$G:$G, "Not Started")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$K:$K, "*"&Q8&"*", 'Testing 2020'!$G:$G, "In Progress")

First off, I couldn't figure out how to do the OR.... "Completed", "Not Started", "In Progress"....so I just spelled it out three times. That is not really my question, but if you could adjust that, just let me know.

OK. I am trying to add the following criteria. [Testing 2020] tab; column L named 'Team'. I have another tab [Teams] with a list of teams in A1:A5. I only want hte count if to count this records if column L - Team is found within my Team list from Teams A1=A5.

 

[Fluff]

For your original formula you can use
=SUM(COUNTIFS('Testing 2020'!$D:$D,"Testing",'Testing 2020'!$AB:$AB,"*"&G8&"*",'Testing 2020'!$K:$K,"*"&Q8&"*",'Testing 2020'!$G:$G,{"Completed","Not Started","In Progress"}))
This may need CSE

 

[kparadise]

Fluff:

I apologize, I di not paste the entire formula because I was not even thinking about fixing that {...,...,...} piece of the formula. Here is the entire formula. So, how would I do that SUM(COUNTIF( array formula with that?

'=COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$K:$K, "*"&Q8&"*", 'Testing 2020'!$G:$G, "Completed")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$K:$K, "*"&Q8&"*", 'Testing 2020'!$G:$G, "Not Started")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$K:$K, "*"&Q8&"*", 'Testing 2020'!$G:$G, "In Progress")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$P:$P, "*"&Q8&"*", 'Testing 2020'!$G:$G, "Completed")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$P:$P, "*"&Q8&"*", 'Testing 2020'!$G:$G, "Not Started")+ COUNTIFS('Testing 2020'!$D:$D, "Testing", 'Testing 2020'!$AB:$AB, "*"&G8&"*", 'Testing 2020'!$P:$P, "*"&Q8&"*", 'Testing 2020'!$G:$G, "In Progress")

 

[tyija1995]

You could use SUMPRODUCT for this too, I think the following would work, most likely could be shortened though (providing it works!)

=SUMPRODUCT(('Testing 2020'!$D:$D="Testing")*(ISNUMBER(SEARCH(G8,'Testing 2020'!$AB:$AB)))*(ISNUMBER(SEARCH(Q8,'Testing 2020'!$K:$K)))*(OR('Testing 2020'!$G:$G="Completed",'Testing 2020'!$G:$G="Not Started",'Testing 2020'!$G:$G="In Progress"))*(OR('Testing 2020'!$L:$L=Teams!A1,'Testing 2020'!$L:$L=Teams!A2,'Testing 2020'!$L:$L=Teams!A3,'Testing 2020'!$L:$L=Teams!A4,'Testing 2020'!$L:$L=Teams!A5)))+SUMPRODUCT(('Testing 2020'!$D:$D="Testing")*(ISNUMBER(SEARCH(G8,'Testing 2020'!$AB:$AB)))*(ISNUMBER(SEARCH(Q8,'Testing 2020'!$P:$P)))*(OR('Testing 2020'!$G:$G="Completed",'Testing 2020'!$G:$G="Not Started",'Testing 2020'!$G:$G="In Progress"))*(OR('Testing 2020'!$L:$L=Teams!A1,'Testing 2020'!$L:$L=Teams!A2,'Testing 2020'!$L:$L=Teams!A3,'Testing 2020'!$L:$L=Teams!A4,'Testing 2020'!$L:$L=Teams!A5)))

 

[kparadise]

Yea, I would love the shorted version....I was hoping I could leverage a range in the TEAMS tab...in case those team names were modified, added, removed, etc.

 

[Fluff]

To include the teams try
=SUM(COUNTIFS('Testing 2020'!$D:$D,"Testing",'Testing 2020'!$AB:$AB,"*"&G8&"*",'Testing 2020'!$K:$K,"*"&Q8&"*",'Testing 2020'!$G:$G,{"Completed","Not Started","In Progress"},'Testing 2020'!L:L,Teams!A1:A5))
You will then need to do the same with the other half of your formula & add them together

 

[kparadise]

It's is now working as an array formula, thank you all!

{=SUM(COUNTIFS('Testing 2020'!$D:$D,"Testing",'Testing 2020'!$AB:$AB,"*"&G9&"*",'Testing 2020'!$K:$K,"*"&Q9&"*",'Testing 2020'!$G:$G,{"Completed","Not Started","In Progress"},'Testing 2020'!$L:$L,'CCB Teams'!$A$2:$A$11) + SUM(COUNTIFS('Testing 2020'!$D:$D,"Testing",'Testing 2020'!$AB:$AB,"*"&G9&"*",'Testing 2020'!$P:$P,"*"&Q9&"*",'Testing 2020'!$G:$G,{"Completed","Not Started","In Progress"},'Testing 2020'!$L:$L,'CCB Teams'!$A$2:$A$11)))}

 

[kparadise]

its an array! Got it!

 

[Fluff]