Forum Discussion

RMF40's avatar
RMF40
Copper Contributor
Dec 05, 2024

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. 

 

 

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    =LET(
    a,FILTER(A2:F8,LEFT(A1:F1)="A"),
    b,TOCOL(a),
    c,FILTER(b,b="X"),
    d,COUNTA(c),
    d)

     

Resources