Wildcards with SumProduct
 

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

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

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

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

Uum, isn't that what it already does?

--

HTH

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


"pomalley" wrote in message
...
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)
)

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" wrote:

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

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 wrote:
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" wrote:


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

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.

"Aladin Akyurek" wrote:

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 wrote:
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" wrote:


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