Countif range is less than or more than another range.

Nightcrawller

New Member
Joined
Sep 1, 2018
Messages
8
I want to count if a range of cells are less than or more than another range of cells.=countif(K12:K491, "<1.9") =countif(J12:J491, "<1.9")this is what i am using now but i want this:=countif(J14:J493<K14:K493) but its a formula phrase error.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Nightcrawller

New Member
Joined
Sep 1, 2018
Messages
8
I want to count if a range of cells are less than or more than another range of cells.=countif(K12:K491, "<1.9") =countif(J12:J491, "<1.9")this is what i am using now but i want this:=countif(J14:J493<K14:K493) but its a formula phrase error.I am sorry i am using mobile browser and i swear i have well presented the whole thread but it just messes up.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Try:

=SUMPRODUCT(--(J14:J493 < K14:K493))<k14:k493))< html=""></k14:k493))<>
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
Thanks bro but can you tell me the point of having this (--) extra encasing bracket?
SUMPRODUCT requires numbers to work with.
J14:J493 < K14:K493 produces an array of TRUE/FALSE logical values
--( ) forces those TRUE/FALSE values to 1/0 values so SUMPRODUCT can add them.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,619
Messages
5,523,938
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top