"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