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

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

Hi

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

Arvi Laanemets


<mike.wilson8@comcast.net> wrote in message
news:1149007022.451501.47590@j55g2000cwa.googlegroups.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
>

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