using a countif with multiple columns

[mike.wilson8]

I'm trying to count records if in col c(c2:c160) the value is =2 and in
col j(j2:j160) the value is between 1-7.

If the criteria matches count that as one record.

Any help would be much appreciated

 

[Pete_UK]

Try this array* formula:

=SUM(IF((C$2:C$160=2)*(J$2:J$160>=1)*(J$2:J$160<=7),1,0))

* As this is an array formula, once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

Hope this helps.

Pete

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--(C2:C160=2),--(J2:J160>=1),--(J2:J160<=7))

Arvi Laanemets

 

[Guest]

Why do you have to use CTRL-SHIFT-ENTER instead of just ENTER? What
difference does that make?

 

[Guest]

Why do you have to use CTRL-SHIFT-ENTER instead of just ENTER? What
difference does that make?