Countif

kreases

Board Regular
Joined
Oct 26, 2005
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 = )
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top