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.
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))