Compare an Array to a Date Range

[Mashuganah]

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?

 

[T. Valko]

Try something like this...

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

 

[Mashuganah]

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?

 

[T. Valko]

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!

 

[Mashuganah]

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

 

[Mashuganah]

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?

 

[Mashuganah]

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.

 

[Mashuganah]

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.

 

[T. Valko]

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.

 

[T. Valko]

Good deal. Thanks for the feedback!