Forum Discussion
Count the number of zero using countif function.
Hi there
I had a simple excel sheet where I would like to count the number of zero in row A, B, C etc and so on. However, I would like to exclude column C to be counted. How would you write such a range? So for example, the countif function would output 0, 1, 1, 1, 1 for row A, B, C, D and E respectively.
6 Replies
If it was me I would consider using a different web browser as yours is obviously buggy:
POST questions => Works
GET solution(s) => Works
POST reply/feedback => Doesn't workThe 2nd POST never reaches this site. Consistent:
- This thread
- Need help to check whether it is dual depot
- Separating one column into two column excel formatting
- Create a dynamic pivottable
Hope this helps 🙂
*** Marking solutions helps people who Search ***For Excel versions < 2021 a SUMPRODUCT option:
in J2:
=SUMPRODUCT(--((COLUMN(A2:H2)-COLUMN(A:A)+1)<>3), --(A2:H2=0))Another 365 approach based upon the Lorenzo interpretation of your problem.
= BYROW(Data, LAMBDA(d, COUNTIFS(d, 0, hdr, "<>field2" ) ) )where "field2" is one of a set of column headers.
A key feature of the solution is that it exploits the fact that BYROW passes a set of Range References, not just an array of numbers.
A key feature of the solution is that it exploits the fact that BYROW passes a set of Range References, not just an array of numbers
Didn't realize/know that and it's really great - Thank you
Hi evilgreenred
Assuming I understood (count by row, excluding 3rd column) with Excel 2021 or 365:
in J2:
=MMULT((Data=0) * (SEQUENCE(,COLUMNS(Data))<>3), SEQUENCE(COLUMNS(Data))^0)=(B1=0)+(D1=0)
