excelcountcountif

How do I count the number of columns that have at least a cell where the number is greater than 0?


Year a b c
2017 0 1 3
2018 0 3 0
2019 0 0 0

Given the table above, what's an excel formula to help me count how many columns have at least one cell that's greater than 1? Would prefer to have a formula that would work if I have 1000 columns.

In this case, the answer should be 2 (columns b and c).

I was trying to use different combinations of COUNTIFS and SUM but have not found the right one.So far have tried

=SUM(--(MAX(B2:B4)>1), --(MAX(C2:C4)>1), --(MAX(D2:D4)>1))

This works but is not feasible with 1000 columns.


Solution

  • If you have Excel 365 you can do it by splitting your data into individual columns and checking in column in turn:

    Verbose:

    =LET(
        data, B2:E4,
        colHasValue, BYCOL(data, LAMBDA(col, MAX(col) > 0))*1,
        SUM(colHasValue)
    )
    

    Compact:

    =SUM(BYCOL(B2:E4, LAMBDA(col, MAX(col) > 0))*1)
    

    for earlier Excel versions you could use:

    =SUM(IF(FREQUENCY(IF(B2:E4>0, COLUMN(B2:E4)), COLUMN(B2:E4)) > 0, 1))