# count number of cells in Column C that have a lesser or equal value to the adjacent cell

1. ## count number of cells in Column C that have a lesser or equal value to the adjacent cell

I need to count the number of cells in Column C that have a lesser or equal value to the adjacent cell in column D excluding cells that have a 0 value in both columns.

So the count for the following sequence would be 2
c1 = 23 and d1 = 24
C2 = 23 and D2 = 23
C3 = 0 and D3 = 0

I've tried using IF(AND and countif but I can't get the syntax right

The formula I came up with was =IF(AND(C:C="0",D:D="0","0",countif(C:C"<="D:D)) which doesn't work

Any help would be most welcome   Register To Reply

2. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

=IF(AND(C1<>0,D1<>0),COUNTIF(C:C,"<" & D1),0)

fill down  Register To Reply

3. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

Thanks, but it returned a count value of 3 and not 2??  Register To Reply

4. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

Maybe: ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

=sum(if(C1:C100>D1:D100),1,0))-sum(if(c1:c100=0,1,0)*if(D1:D100=0,1,0))

as an array formula (confirm with ctrl+shift+enter)  Register To Reply

6. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

apologies, it counts the 0's as they are less. THe array formula is a good way.  Register To Reply

7. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

=sum((\$c\$1:\$c\$26<d1)*(\$c\$1:\$c\$26<>0)*(\$d\$1:\$d\$26<>0)*1)  Register To Reply

8. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

Hi Yudlugar, Unfortunately that reported a function error (highlighting the D100 in the first range) However the array that Nathansav posted at 10.00a.m works fine.   Register To Reply

9. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

Hi

That works fine

Thank you so much for your help and thanks to all contributors who responded.   Register To Reply

10. ## Re: count number of cells in Column C that have a lesser or equal value to the adjacent c

Try
=SUMPRODUCT(--(C1:C1000<=D1:D100),SIGN(C1:C100*D1:D100),SIGN(C1:C100*D1:D100))  Register To Reply