Simplify COUNTIFS Formula

Question

  A  B  C  D
1 Y  Y  Y  "332212"
2 Y  N  Y  "231223"
3 N  Y  Y  "122131"
4 Y  Y  Y  "231132"

Column A, B and C indicate whether a test was completed (Y=yes, N=no).

The test is in two-parts and column D holds the results:

• the first two digits of column D hold the results for column A, i.e. "AA----"
• the middle two digits of column D hold the results for column B, i.e. "--BB--"
• the last two digits of column D hold the results for column C, i.e. "----CC"

I am trying to separately count the cells in column A, B and C containing a Y that:

• contain 22, 23 or 32, in the appropriate position of column D
• contain 11, 12, 21, 13 or 31, in the appropriate position of column D

I have the following which works, but I am curious whether there's a simpler way to accomplish the same:

=COUNTIFS(A,"=Y",D,"=22*")   + COUNTIFS(A,"=Y",D,"=23*")   + COUNTIFS(A,"=Y",D,"=32*")
+COUNTIFS(B,"=Y",D,"=??22*") + COUNTIFS(B,"=Y",D,"=??23*") + COUNTIFS(B,"=Y",D,"=??32*")
+COUNTIFS(C,"=Y",D,"=*22")   + COUNTIFS(C,"=Y",D,"=*23")   + COUNTIFS(C,"=Y",D,"=*32")

=COUNTIFS(A,"=Y",D,"=1*")    + COUNTIFS(A,"=Y",D,"=?1*")
+COUNTIFS(B,"=Y",D,"=???1*") + COUNTIFS(B,"=Y",D,"=???1*") 
+COUNTIFS(C,"=Y",D,"=*1")    + COUNTIFS(C,"=Y",D,"=*?1")

Answer 1

I suggest parsing a copy of ColumnD with Text to Columns (Fixed width: "|nn|nn|nn| ) and:

in I1: =IF(AND(A1="Y",OR(E1=22,E1=23,E1=32)),1,"")
in N1: =IF(AND(A1="Y",OR(E1=11,E1=12,E1=21,E1=13,E1=31)),1,"")

each copied across two columns to the right and down to suit, then something like =SUM(I1:K4) and =SUM(N1:P4).

Answer 2

If I understand you question correctly, you can seperate column d using the mid function

=mid(column d, 1, 2) will give you the first two digits in column d.... =mid( column d, 2, 2) the second 2, etc.

you can count from there.