Excel count between two array constants - only returning first three instances

Question

I am trying to count only completed components in a manufacturing shift report.

Example: I have 6 components (each having three parts) in column A represented by array constant {1,2,3,4,5,6} (in other words this column repeats three times per component - 1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6)

I have three parts per component in column B represented by array constant {"a","b","c"}. (in other words component 1 has an "a" part on one line, a "b" part on the next line and a "c" part on the third line in the shift print report.

I also have a status/ critera comment in column C represented by "Complete"

My formula trying to count only completed components (each having it's three parts) is: {=sum(countifs(A:A,{1,2,3,4,5,6},B:B,{"a","b","b"},C:C,"Complete"))}

THIS HOWEVER ONLY WORKS FOR THE FIRST THREE COMPONENTS, i.e. NOT 4,5,6.

Please help with this as my full application would be up to a hundred components each shift each with three parts that I must report as ready for despatch only when column C is "Complete".

I am doing this manually at this time which is massively numbing work.

An alternative formula I tried but which only works if A:A has one value: {=sum(if((A:A={1,2,3,4,5,6})*(B:B={"a","b","c"}),1,0))}

I am totally confounded by this one - awesomely braintwisted!

I tried looking for resources on mismatched array constants, which I thought relevant in this case but do not understand how to proceed (anyway).

Please can some one show me the light!

Thanks, Stu

XOR LX · Accepted Answer · 2015-03-17 15:42:11Z

2

Try transposing one (but not both) of your array constants so that it is orthogonal to the other, i.e. either:

=SUM(COUNTIFS(A:A,{1,2,3,4,5,6},B:B,{"a";"b";"c"},C:C,"Complete"))

or:

=SUM(COUNTIFS(A:A,{1;2;3;4;5;6},B:B,{"a","b","c"},C:C,"Complete"))

Note that these constructions do not require committing as array formulas, i.e. with CSE.

If you're interested in an explanation as to this required syntax, see here:

http://excelxor.com/2014/09/28/countifs-multiple-or-criteria-for-one-or-two-criteria_ranges/

Regards

answered Mar 17, 2015 at 15:42

XOR LX

7,7821 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Stuart Over a year ago

Thanks for you feedback - I am checking the reference now.

Steven Martin Over a year ago

i think he meant orthogonal :)

Stuart Over a year ago

Hi XOR LX, both formulas in your answer are the same? I still get three as the value, this is a tough one!

XOR LX Over a year ago

@Stuart Does your version of Excel use the comma or semi-colon as argument separator within functions?

barry houdini Over a year ago

"array"? No that's a new one for me too

|

Collectives™ on Stack Overflow

Excel count between two array constants - only returning first three instances

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related