=SUMPRODUCT(--(LEFT($W$3:$W$200,3)=A3),--(DATE(YEAR($U$3:$U$200),MONTH($U$3:$U$200),1)=B3), $X$3:$X$200)

where A3 houses a value like USA and B3 a month/year date set to its
first day, e.g., 1-Feb-2004.

pomalley wrote:
Pretty terrific stuff. Now, let's suppose that the month is really a date.
I added a column with the month in it as text, but I'd like to avoid having
to do that. Rather I'd like to perform the same operation but instead, ask
it to locate only records within the month of January. In other words, there
are 10 entries, each with a different January day of the month. Any
suggestions. Thanks again.

"Bob Phillips" wrote:


=SUMPRODUCT(--(LEFT($w$3:$w$200,3)="USA"),--(TEXT($U$3:$U$200,"mmm")="Jan"),
$X$3:$X$200)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"pomalley" wrote in message
...

I'm using the following formula, but need to select groups whose names all
start with USA but end with different dept names, such as USA-Travel,
USA-Resort, USA-Hotel. There are approximately 10 dept's that start with
USA. Additionally, the sum should be for those dept's who have activity
during a particular month like Jan. Is there a way to include a wild card

to

search/find all dept names that start with USA? I've tried using LEFT

with 3

characters designated, but SUMPRODUCT doesn't seem to like it much.



=SUMPRODUCT(--($w$3:$w$200="USA-Travel"),--($U$3:$U$200="Jan"),($X$3:$X$200)
)