using a countif with multiple columns
 

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

using a countif with multiple columns
 

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

using a countif with multiple columns
 

Hi

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

Arvi Laanemets


wrote in message
ups.com...
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

using a countif with multiple columns
 

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

"Pete_UK" wrote:

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