Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to print multiple columns on same page | Excel Discussion (Misc queries) | |||
sorting data across multiple columns | Excel Discussion (Misc queries) | |||
COUNTIF - multiple conditions | Excel Worksheet Functions | |||
Pivot Table combining multiple columns | Excel Discussion (Misc queries) | |||
Filtering Text Data from Multiple columns | Excel Worksheet Functions |