# Using COUNTIF function if value to be counted includes < or >

#### Human_doing

##### Board Regular
Hi everybody,

Can anyone please correct below formula so it will count all instances of >18 in the range? Seems like Excel interprets the > as being part of the calculations rather than part of the string to be searched for and I'm not sure how to sort this. Formula at present is:

=COUNTIF(B2:B51, "<18")

Thanks in advance for any help!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi

Try:

=COUNTIF(B2:B51, "=<18")

Hello, maybe: =COUNTIFS

Hi everybody,

Can anyone please correct below formula so it will count all instances of >18 in the range? Seems like Excel interprets the > as being part of the calculations rather than part of the string to be searched for and I'm not sure how to sort this. Formula at present is:

=COUNTIF(B2:B51, "<18")

Thanks in advance for any help!

If you are wanting to count the occurrences of the literal string of >18, try:

=SUMPRODUCT((B2:B51=">18")+0)

Hi everybody,

Can anyone please correct below formula so it will count all instances of >18 in the range? Seems like Excel interprets the > as being part of the calculations rather than part of the string to be searched for and I'm not sure how to sort this. Formula at present is:

=COUNTIF(B2:B51, "<18")

Thanks in advance for any help!

Your formula is looking for anything that is less than 18 in the range, if you change the < to > then it will give you anything that is above 18.

Thanks guy for your help, worked perfectly.

Replies
3
Views
164
Replies
1
Views
102
Replies
1
Views
180
Replies
9
Views
265
Replies
1
Views
262

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back