Compare an Array to a Date Range
 

I'm currently using the below array equation to count records on one sheet
that match certain criteria on another sheet:
{=COUNT(MATCH(Tickets!G:G, 'Staff'!A2:A15, 0))}

I'm not sure how to modify it so that it also only counts records found in a
given month, e.g., return a count of matching records that were created in
March. There is a date field associated with each record.

Ideas?

Compare an Array to a Date Range
 

Try something like this...

=SUMPRODUCT(--(MONTH(date_range)=3),--(ISNUMBER(MATCH(Tickets!G1:G100,'Staff'!A2:A15,0)) ))

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
I'm currently using the below array equation to count records on one sheet
that match certain criteria on another sheet:
{=COUNT(MATCH(Tickets!G:G, 'Staff'!A2:A15, 0))}

I'm not sure how to modify it so that it also only counts records found in
a
given month, e.g., return a count of matching records that were created in
March. There is a date field associated with each record.

Ideas?

Compare an Array to a Date Range
 

Biff,

Thanks! I implemented your modification as:
{=SUMPRODUCT(--(MONTH(Tickets!A:A)=3),--(ISNUMBER(MATCH(Tickets!G:G,Staff!A2:A15,0))))}

However, it produces a #VALUE! error. Ideas?

Compare an Array to a Date Range
 

What version of Excel are you using?

Unless you're using Excel 2007 or later you can't use entire columns as
range references with SUMPRODUCT.

However, that won't cause a #VALUE! error, it would return a #NUM! error.
Chances are the #VALUE! error is coming from:

MONTH(Tickets!A:A)=3

Are there any TEXT entries in Tickets!A:A? The MONTH function will return an
error if any of the references are TEXT.

MONTH(3/1/2010) = 3
MONTH(text) = #VALUE!

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
Biff,

Thanks! I implemented your modification as:
{=SUMPRODUCT(--(MONTH(Tickets!A:A)=3),--(ISNUMBER(MATCH(Tickets!G:G,Staff!A2:A15,0))))}

However, it produces a #VALUE! error. Ideas?

Compare an Array to a Date Range
 

Biff,

I've broken your equation in half and each half appears to work
independently, i.e., the MONTH equation and the ISNUMBER equation. However,
when they're put together under SUMPRODUCT I get the #Value error.

Greg

Compare an Array to a Date Range
 

Biff,

I'm using Excel 2007. I changed the full column designation to a range to
avoid the text in the header, but I get the same error:

{=SUMPRODUCT(--(MONTH(Tickets!A2:A25000)=3),--(ISNUMBER(MATCH(Tickets!G:G,Staff!A:A,0))))}

I tried eyeballing the records for text but there are 25,000 of them. Is
there a way to test for text in a range?

Compare an Array to a Date Range
 

Update:

I just re-entered the equation with specific ranges for all values and it
returned a zero. Zero records is not correct.

=SUMPRODUCT(--(MONTH(Tickets!A2:A25000)=10),--(ISNUMBER(MATCH(Tickets!G2:G25000,TAC Staff!A2:A15,0))))

Yes, there are records from October.

Compare an Array to a Date Range
 

Biff,

I finally got it to work. The final form was:
=SUMPRODUCT(--(MONTH(Tickets!A2:A25000)=10),--(ISNUMBER(MATCH(Tickets!G2:G25000,'TAC Staff'!A2:A15,0))))

Thanks.

Compare an Array to a Date Range
 

See this:

http://contextures.com/xlFunctions02.html#Trouble

Even though that link describes the use of a different function, the
problems explained there, data type mismatches, apply to all functions.

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
Update:

I just re-entered the equation with specific ranges for all values and it
returned a zero. Zero records is not correct.

=SUMPRODUCT(--(MONTH(Tickets!A2:A25000)=10),--(ISNUMBER(MATCH(Tickets!G2:G25000,TAC
Staff!A2:A15,0))))

Yes, there are records from October.

Compare an Array to a Date Range
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
Biff,

I finally got it to work. The final form was:
=SUMPRODUCT(--(MONTH(Tickets!A2:A25000)=10),--(ISNUMBER(MATCH(Tickets!G2:G25000,'TAC
Staff'!A2:A15,0))))

Thanks.