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

try this where a1 has your date formatted the same

=sumproduct((ai2:ai22=1234)*(am2:am22=a1)*(aj2:aj22=-1))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Charlie510" <(E-Mail Removed)> wrote in message
news:2D7A8680-D7B9-424F-89CB-(E-Mail Removed)...
> 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

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 <Charlie...@discussions.microsoft.com>
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

> 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" <(E-Mail Removed)> wrote in message
news:2D7A8680-D7B9-424F-89CB-(E-Mail Removed)...
> 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