Wildcards with SumProduct

[pomalley]

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))

 

[Bob Phillips]

=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]

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.

 

[Aladin Akyurek]

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

 

[Bob Phillips]

Uum, isn't that what it already does?

--

HTH

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

 

[pomalley]

I'm having difficulty with your equation. I'm not clear about the date
string. Using this function results in a #VALUE error. I was careful to
format the b3 cell as indicated in your note, but it still results in the
error. What does the 1 identify, is that for first month, first day of
month? Any suggestions you have are greatly appreciated.

 

[Aladin Akyurek]

Lets say that the condition month is 1, meaning January. Lets also
suppose that the year is 2005. In B3 enter:

1/1/2005

The condition date is thus set to the first day date of the Jan-2005.

 

[pomalley]

Well, it works like a charm. I still would like to contain the query within
the string rather than create columns of sort criteria, but this will work
nicely. Thanks again for all your help.