Forum Discussion
Solved
How do I count the cells in a column where the column header starts with a character?
I have a table where the value in the top row for each column of data starts with either A, H or L. Something like, A_LIT, A__LANG, A_SCI.
What I want to do is to count all the cells in each column where the value in the top row starts with a given value and the cell contains a given value; for example the header starts with an 'A' and the cell contains with an x.
I know how to do this if the column header actually equals a value but not if the column header starts with a particular value.
As variant
=SUMPRODUCT( ($A$2:$F$8="x") * (LEFT($A$1:$F$1) = "A") )
2 Replies
As variant
=SUMPRODUCT( ($A$2:$F$8="x") * (LEFT($A$1:$F$1) = "A") )=LET( a,FILTER(A2:F8,LEFT(A1:F1)="A"), b,TOCOL(a), c,FILTER(b,b="X"), d,COUNTA(c), d)
