Compare an Array to a Date Range

M

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

T. Valko

Try something like this...

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

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

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!
 
M

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
 
M

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?
 
M

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.
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top