Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with Date Range Array | Excel Discussion (Misc queries) | |||
Return array of data by date range | Excel Discussion (Misc queries) | |||
Return an array of data based on range of date | Excel Worksheet Functions | |||
date range table array formula | Excel Worksheet Functions | |||
Compare date of a transaction against a range of dates | Excel Worksheet Functions |