Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells with multiple criteria
I'm trying to count the number of cells that meet criteria in multiple columns:
Column AI contains a job number Column AJ contains "blank", "0", or "-1" Column AM contains the mm/dd/yyyy the job was finished I need a COUNT of the cells in AJ that = "-1" and for which the job was finished in a certain month |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells with multiple criteria
try this where a1 has your date formatted the same =sumproduct((ai2:ai22=1234)*(am2:am22=a1)*(aj2:aj2 2=-1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Charlie510" wrote in message ... I'm trying to count the number of cells that meet criteria in multiple columns: Column AI contains a job number Column AJ contains "blank", "0", or "-1" Column AM contains the mm/dd/yyyy the job was finished I need a COUNT of the cells in AJ that = "-1" and for which the job was finished in a certain month |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells with multiple criteria
Try this:
=SUMPRODUCT((MONTH(AM1:AM100)=1)*(YEAR(AM1:AM100)= 2008)*(AJ1:AJ100=-1)) I've assumed you have 100 rows of data - adjust the ranges if you have more. This checks for Month 1 (January) and for this year, so you can adjust these if necessary (or put them in a separate cell and use the cell reference). I've also assumed that you have the number -1 in column AJ, rather than the text value "-1". Hope this helps. Pete On Aug 26, 4:54*pm, Charlie510 wrote: I'm trying to count the number of cells that meet criteria in multiple columns: Column AI contains a job number Column AJ contains "blank", "0", or "-1" Column AM contains the mm/dd/yyyy the job was finished I need a COUNT of the cells in AJ that = "-1" and for which the job was finished in a certain month |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells with multiple criteria
Column AM contains the mm/dd/yyyy the job was finished
I need a COUNT of the cells in AJ that = "-1" and for which the job was finished in a certain month So the year can be *any* year? =SUMPRODUCT(--(AJ1:AJ10=-1),--(AM1:AM10<""),--(MONTH(AM1:AM10)=n)) Where n = the month number: Jan = 1 , Dec = 12 -- Biff Microsoft Excel MVP "Charlie510" wrote in message ... I'm trying to count the number of cells that meet criteria in multiple columns: Column AI contains a job number Column AJ contains "blank", "0", or "-1" Column AM contains the mm/dd/yyyy the job was finished I need a COUNT of the cells in AJ that = "-1" and for which the job was finished in a certain month |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells basesd on multiple criteria | New Users to Excel | |||
Counting cells using multiple criteria | Excel Worksheet Functions | |||
Counting multiple cells using a criteria | Excel Discussion (Misc queries) | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |