Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
Hello,
I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
=Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy")))
So you want to count dates within a certain month of a certain year. A1 = start date = 1/1/2009 B1 = end date = 1/31/2009 =SUMPRODUCT(--(Overall!H8:H2200=A1),--(Overall!H8:H2200<=B1)) to calculate more than one date range? You'll have to provide more detail. -- Biff Microsoft Excel MVP "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
Hi,
What exactly do you mean by more than one date range? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "roy.okinawa" wrote: Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
"roy.okinawa" wrote:
=Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
=sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")),--(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy")))
Are you sure that's doing what you intended? Let's assume: A1 = 2/15/2009 A2 = 6/15/2009 That will count dates in Feb that are <A1 and dates in Jun that are A2. It's evaluating text strings not date serial numbers.. =TEXT("2/1/2009","mm/yyyy") = "02/2009" =TEXT("2/15/2009","mm/yyyy") = "02/2009" "02/2009"=TEXT(A1,"mm/yyyy") = TRUE "02/2009"<=TEXT(A2,"mm/yyyy") = TRUE ="02"<"03" = TRUE ="02""03" = FALSE ="A"<"B" = TRUE ="A""B" = FALSE -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "roy.okinawa" wrote: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
"T. Valko" wrote:
=sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? [....] It's evaluating text strings not date serial numbers. Right. Change all uses of TEXT to --TEXT. Simplifying: =sumproduct((--text(overall!H8:H2200,"mm/yyyy")=--text(A1,"mm/yyyy")) * (--text(overall!H8:H2200,"mm/yyyy")<=--text(A2,"mm/yyyy"))) Note that comma (",") is replaced with multipy ("*"). ----- original message ----- "T. Valko" wrote in message ... =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")),--(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? Let's assume: A1 = 2/15/2009 A2 = 6/15/2009 That will count dates in Feb that are <A1 and dates in Jun that are A2. It's evaluating text strings not date serial numbers.. =TEXT("2/1/2009","mm/yyyy") = "02/2009" =TEXT("2/15/2009","mm/yyyy") = "02/2009" "02/2009"=TEXT(A1,"mm/yyyy") = TRUE "02/2009"<=TEXT(A2,"mm/yyyy") = TRUE ="02"<"03" = TRUE ="02""03" = FALSE ="A"<"B" = TRUE ="A""B" = FALSE -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "roy.okinawa" wrote: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
After thinking about this overnight, you were correct.
I forgot that we're looking for month year *NOT* day month year. My bad! -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "T. Valko" wrote: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? [....] It's evaluating text strings not date serial numbers. Right. Change all uses of TEXT to --TEXT. Simplifying: =sumproduct((--text(overall!H8:H2200,"mm/yyyy")=--text(A1,"mm/yyyy")) * (--text(overall!H8:H2200,"mm/yyyy")<=--text(A2,"mm/yyyy"))) Note that comma (",") is replaced with multipy ("*"). ----- original message ----- "T. Valko" wrote in message ... =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")),--(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? Let's assume: A1 = 2/15/2009 A2 = 6/15/2009 That will count dates in Feb that are <A1 and dates in Jun that are A2. It's evaluating text strings not date serial numbers.. =TEXT("2/1/2009","mm/yyyy") = "02/2009" =TEXT("2/15/2009","mm/yyyy") = "02/2009" "02/2009"=TEXT(A1,"mm/yyyy") = TRUE "02/2009"<=TEXT(A2,"mm/yyyy") = TRUE ="02"<"03" = TRUE ="02""03" = FALSE ="A"<"B" = TRUE ="A""B" = FALSE -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "roy.okinawa" wrote: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
"T. Valko" wrote:
After thinking about this overnight, you were correct. I forgot that we're looking for month year *NOT* day month year. No, you were correct in the first place! Consider the comparison of the dates 02/2009 < 04/2008 in text form. That would be TRUE, which is not the intent. I don't remember if the OP qualified the dates in question so that a text comparison would work by coincidence. I could look, but.... But I agree with your first response: it is more reliable to do a numeric comparison of the date serial numbers. That is certainly what I had in mind when I posted my formulas originally. My hyphen key was simply broken at the time ;). ----- original message ----- "T. Valko" wrote in message ... After thinking about this overnight, you were correct. I forgot that we're looking for month year *NOT* day month year. My bad! -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "T. Valko" wrote: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? [....] It's evaluating text strings not date serial numbers. Right. Change all uses of TEXT to --TEXT. Simplifying: =sumproduct((--text(overall!H8:H2200,"mm/yyyy")=--text(A1,"mm/yyyy")) * (--text(overall!H8:H2200,"mm/yyyy")<=--text(A2,"mm/yyyy"))) Note that comma (",") is replaced with multipy ("*"). ----- original message ----- "T. Valko" wrote in message ... =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")),--(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? Let's assume: A1 = 2/15/2009 A2 = 6/15/2009 That will count dates in Feb that are <A1 and dates in Jun that are A2. It's evaluating text strings not date serial numbers.. =TEXT("2/1/2009","mm/yyyy") = "02/2009" =TEXT("2/15/2009","mm/yyyy") = "02/2009" "02/2009"=TEXT(A1,"mm/yyyy") = TRUE "02/2009"<=TEXT(A2,"mm/yyyy") = TRUE ="02"<"03" = TRUE ="02""03" = FALSE ="A"<"B" = TRUE ="A""B" = FALSE -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "roy.okinawa" wrote: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
Ok, I'm suffering from major brain cramps today!
If the dates to be counted are all within the same year then your formula works. I was getting confused about how Excel evaluates text precedence. "02/2009" is less than "03/2000" -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "T. Valko" wrote: After thinking about this overnight, you were correct. I forgot that we're looking for month year *NOT* day month year. No, you were correct in the first place! Consider the comparison of the dates 02/2009 < 04/2008 in text form. That would be TRUE, which is not the intent. I don't remember if the OP qualified the dates in question so that a text comparison would work by coincidence. I could look, but.... But I agree with your first response: it is more reliable to do a numeric comparison of the date serial numbers. That is certainly what I had in mind when I posted my formulas originally. My hyphen key was simply broken at the time ;). ----- original message ----- "T. Valko" wrote in message ... After thinking about this overnight, you were correct. I forgot that we're looking for month year *NOT* day month year. My bad! -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "T. Valko" wrote: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? [....] It's evaluating text strings not date serial numbers. Right. Change all uses of TEXT to --TEXT. Simplifying: =sumproduct((--text(overall!H8:H2200,"mm/yyyy")=--text(A1,"mm/yyyy")) * (--text(overall!H8:H2200,"mm/yyyy")<=--text(A2,"mm/yyyy"))) Note that comma (",") is replaced with multipy ("*"). ----- original message ----- "T. Valko" wrote in message ... =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")),--(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? Let's assume: A1 = 2/15/2009 A2 = 6/15/2009 That will count dates in Feb that are <A1 and dates in Jun that are A2. It's evaluating text strings not date serial numbers.. =TEXT("2/1/2009","mm/yyyy") = "02/2009" =TEXT("2/15/2009","mm/yyyy") = "02/2009" "02/2009"=TEXT(A1,"mm/yyyy") = TRUE "02/2009"<=TEXT(A2,"mm/yyyy") = TRUE ="02"<"03" = TRUE ="02""03" = FALSE ="A"<"B" = TRUE ="A""B" = FALSE -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "roy.okinawa" wrote: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct of date range
"T. Valko" wrote:
Ok, I'm suffering from major brain cramps today! I know that feeling all too well :). ----- original message ----- "T. Valko" wrote in message ... Ok, I'm suffering from major brain cramps today! If the dates to be counted are all within the same year then your formula works. I was getting confused about how Excel evaluates text precedence. "02/2009" is less than "03/2000" -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "T. Valko" wrote: After thinking about this overnight, you were correct. I forgot that we're looking for month year *NOT* day month year. No, you were correct in the first place! Consider the comparison of the dates 02/2009 < 04/2008 in text form. That would be TRUE, which is not the intent. I don't remember if the OP qualified the dates in question so that a text comparison would work by coincidence. I could look, but.... But I agree with your first response: it is more reliable to do a numeric comparison of the date serial numbers. That is certainly what I had in mind when I posted my formulas originally. My hyphen key was simply broken at the time ;). ----- original message ----- "T. Valko" wrote in message ... After thinking about this overnight, you were correct. I forgot that we're looking for month year *NOT* day month year. My bad! -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "T. Valko" wrote: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? [....] It's evaluating text strings not date serial numbers. Right. Change all uses of TEXT to --TEXT. Simplifying: =sumproduct((--text(overall!H8:H2200,"mm/yyyy")=--text(A1,"mm/yyyy")) * (--text(overall!H8:H2200,"mm/yyyy")<=--text(A2,"mm/yyyy"))) Note that comma (",") is replaced with multipy ("*"). ----- original message ----- "T. Valko" wrote in message ... =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")),--(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) Are you sure that's doing what you intended? Let's assume: A1 = 2/15/2009 A2 = 6/15/2009 That will count dates in Feb that are <A1 and dates in Jun that are A2. It's evaluating text strings not date serial numbers.. =TEXT("2/1/2009","mm/yyyy") = "02/2009" =TEXT("2/15/2009","mm/yyyy") = "02/2009" "02/2009"=TEXT(A1,"mm/yyyy") = TRUE "02/2009"<=TEXT(A2,"mm/yyyy") = TRUE ="02"<"03" = TRUE ="02""03" = FALSE ="A"<"B" = TRUE ="A""B" = FALSE -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "roy.okinawa" wrote: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) [....] What do I need to add to this formula in order for it to calculate more than one date range? As you probably figured, the obvious solutions, the AND() and OR() functions, do not work as intended. But the following has the same effect. To count the dates in H8:H2200 between the month/year of the dates in A1 and A2 inclusive: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")), --(text(overall!H8:H2200,"mm/yyyy")<=text(A2,"mm/yyyy"))) To count the dates in H8:H2200 that are in the same month/year as the dates in A1 or A2: =sumproduct(--(text(overall!H8:H2200,"mm/yyyy")=text(A1,"mm/yyyy")) + --(text(overall!H8:H2200,"mm/yyyy")=text(A2,"mm/yyyy"))) Note: If A1 and A2 already have dates that are the 1st of the month, no matter how they appear -- temporarily change their format to the custom format mm/dd/yyyy to confirm -- you might consider the following simplification: =sumproduct(--(--text(overall!H8:H2200,"mm/yyyy")=A1)) You apply that to the other paradigms above. ----- original message ----- "roy.okinawa" wrote in message ... Hello, I use this formula to sumproduct for one date range: =Sumproduct(--(Text(Overall!H8:H2200,"mmm/yyyy")=Text(A1,"mmm/yyyy"))) I know it reads "text" but it works. What do I need to add to this formula in order for it to calculate more than one date range? Thanks. Roy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with Date Range | Excel Worksheet Functions | |||
Sumproduct - Date Range | Excel Discussion (Misc queries) | |||
Sumproduct on date range | Excel Discussion (Misc queries) | |||
sumproduct between date range | Excel Worksheet Functions | |||
Sumproduct with date range | Excel Worksheet Functions |