# Countif

#### kreases

##### Board Regular
I am sure this is an easy one,

I want to count a cell entry if it is greater than or equal to an entry in a cell on the same row for example

a1 to a6 have percentage figures as do c1 to c6,

99.75 341 100.00
99.50 5946 99.93
99.75 26020 99.67
99.75 26090 99.67
100.00 25802 99.68
100.00 40134 99.50

I would expect a count of '2' for the above as only 2 cells in the range c1 to c6 are greater than or equal to the entry in column a on that same row. Does that make sense?

John.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### JohnLee

##### Board Regular
I don't think you can use countif in this context. Syntax is COUNTIF(RANGE,"operator"&value) - where operator is ">", "=" etc, don't think the value can be a range.

John

#### DonkeyOte

##### MrExcel MVP
commit as an array (CTRL + SHIFT + ENTER)

=COUNT(IF(K3:K6<M3:M6,1))

once array set will appear as {=COUNT(....)}

or use SUMPRODUCT (no array commit required0

=SUMPRODUCT(--(\$K3:\$K6<\$M\$3:\$M\$6))

EDIT: array formula not display correctly... I would go with SUMPRODUCT anyway

#### kreases

##### Board Regular
I have used the Sumproduct formula and that worked fine once I had put the greater than sign in the right way round , thanks very much.

John.

#### DonkeyOte

##### MrExcel MVP
sorry my column references were incorrect, should read A and C

(A<=C is the same as C>=A though so the operator should not have been an issue though I was missing the = )

#### JohnLee

##### Board Regular
=SUMPRODUCT(--(\$K3:\$K6<\$M\$3:\$M\$6))

Can you explain the above syntax please?

Out of interest how can you tell if a cell is in an array, or if there are any arrays already defined on a worksheet?

Thanks in anticipation!

John

Replies
7
Views
497
Replies
9
Views
248
Replies
0
Views
201
Replies
23
Views
748
Replies
0
Views
428

1,190,918
Messages
5,983,578
Members
439,852
Latest member
balasat

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