# 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

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

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??

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

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)

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.

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)

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.

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.

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1