Excel counts the number of 0s between 1 in a column

If the number of columns is in column a, enter it elsewhere:

countif(a:a,0)

You can count it.

Hehe! Thank you "frog" for the reminder, it's missing an n, it should be countif.

However, the macro ** you said, there is a shorter one, which can also achieve the effect.

sub tj()

dim r as integer, i as integercolumns(3).clearcontentsr = 1for i = 1 to [a65536].end(xlup).

rowif cells(i, 1) = 0 thencells(r, 3) = cells(r, 3) +1else: r = [c65536].end(xlup).row + 1

end if

nextend sub

Assuming they are all in column A, the formula is =CountIF(A1:A10,"0")

Use one of the following formulas to calculate the number of cells with a1:a20 range not 0 =sumproduct(--a1:a20<>0))=sumproduct((a1:

a20<>0)+0)=sumproduct((a1:a20<>0)-0)=sumproduct((a1:a20<>0)*1)=sumproduct((a1:

a20<>0)/1)=sumproduct((a1:a20<>0)^1)=sumproduct(n(a1:a20<>0))

Here's how:

1.Find the number of cells in column A that are not 0:

2.The input formula is as follows:

3.The results are as follows:

If the number of a10:a20 is not 0, then enter.

countif(a10:a20,"<>0")

sum(--subtotal(9,offset($a$1:$a$10,,row($1:$12)-1,10,))=0))

Array formula, ending the input with ctrl+shift+enter.

Number of columns with a statistic of 0:

2.If it is displayed as 0, you can count if (range, 0) to see the number of zeros.

3.Directly count the quantity greater than 0, countif(range,"＞0")。

If it is not resolved, please contact us in time.

I don't really understand what the subject means, it is the total number of months in which employee 1 and employee 4 have no performance for a total of 12 months or a certain month, if there is no performance in any middle workers, it will be counted as a month. To put it bluntly, for example, column B is counted as a month or 7 months without performance, and column C is counted as 6 months without performance and not without performance.

If it's the former, the formula:

countblank(b3:m9)

If it's the latter, the formula:

count(0/(mmult(transpose(n((b3:m9="")))row(3:9)^0)=7))

Press Ctrl+Shift+Enter at the same time to enter the array formulaAll in all, the difficulty is that you don't know what data to count.

Type or copy-paste the following formula in L2: =if(sumProduct((c:c=.)"")*(h:h=1))=0,"",sumif(h:h,j2,g:g)) pull-down padding.

This can be seen as finding the non-null quantity of the target condition.

Number of months in which employee 1 has no performance = countif(b3:m3,"*"Employee 2=countif(b4:m4,"*"）..

And so on.