countif in excel based on column header

Question

How to count TtlA, TtlB and TtlC base on column header? Thanks

 |TtlA |TtlB | TtlC | A | B | C | A | B | C | A | B | C |
 |  3  |  2  |  2   | x | x |   | x |   | x | x | x | x |

Answer 1

Try using the SUMPRODUCT() function. If you setup your sheet like I did then this formula works like a gem. The RIGHT() function was meant to be dynamic so long as you title each column the same, otherwise it can be replaced with static values like, "A", "B" or "C". (Have to watch those "$" refs!)

=SUMPRODUCT(($D$1:$L$1=RIGHT(A$1,1))*($D2:$L2="X"))

Screen capture: Excel 2013

Answer 2

|  A  |  B  |  C   | D | E | F | G | H | I | J | K | L |
-------------------------------------------------------
|TtlA |TtlB | TtlC | A | B | C | A | B | C | A | B | C |
|  3  |  2  |  2   | x | x |   | x |   | x | x | x | x |

Formula for Cell A2:

=COUNTIFS(D2,"x")+COUNTIF(G2,"x")+COUNTIF(J2,"x")

Fill this formula to the right to cell C2

Answer 3

Use this formula in E2 for a vertical count,

=COUNTIF(INDEX($A:$C, 0, MATCH(RIGHT(E$1, 1), $A$1:$C$1, 0)), "X")

Fill right.

        

Use this formula in A2 for a horizontal count.

=COUNTIFS($D1:$L1,RIGHT(A1,1),$D2:$L2,"X")

Fill right.