Im trying to count the number of products in column B for only those that
contain the characters €œvss€� or €œnggf€� which apprear at the end of the
product name. The products are listed as toy-vss, toy-nggf, and toy-ita. It
appears that the formula below works, but only looks at the €œvss€� query and
ignores the €œnggf€� query. Is there a way to combine the query so if €œvss€� or
€œnggf€� are in Column B, it will count those products and give me the total
number of occurences? Im thinking combining the names in a string, but have
not been successful in combining them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005 Data'!$A$2:$A$251),MONTH('[TDS
Wkly Rpt 2005.xls]2005 Data'!$A$2:$A$251),1)=$FX$3),
--(LEFT('[TDS Wkly Rpt 2005.xls]2005 Data'!$AA$2:$AA$251,5)="Other"))))

Hi!

Here's an abbreviated version that will do what you want:

=SUMPRODUCT(--(RIGHT(B2:B251,4)={"-vss","nggf"}))

Just replace your "IF's" with what I have above.

Biff

"pomalley" wrote in message
...
I'm trying to count the number of products in column B for only those that
contain the characters "vss" or "nggf" which apprear at the end of the
product name. The products are listed as toy-vss, toy-nggf, and toy-ita.
It
appears that the formula below works, but only looks at the "vss" query
and
ignores the "nggf" query. Is there a way to combine the query so if "vss"
or
"nggf" are in Column B, it will count those products and give me the total
number of occurences? I'm thinking combining the names in a string, but
have
not been successful in combining them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005 Data'!$A$2:$A$251),MONTH('[TDS
Wkly Rpt 2005.xls]2005 Data'!$A$2:$A$251),1)=$FX$3),
--(LEFT('[TDS Wkly Rpt 2005.xls]2005 Data'!$AA$2:$AA$251,5)="Other"))))

you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

Im trying to count the number of products in column B for only
those that contain the characters €œvss€� or €œnggf€� which
apprear at the end of the product name. The products are listed as
toy-vss, toy-nggf, and toy-ita. It appears that the formula below
works, but only looks at the €œvss€� query and ignores the
€œnggf€� query. Is there a way to combine the query so if
€œvss€� or €œnggf€� are in Column B, it will count those products
and give me the total number of occurences? Im thinking combining
the names in a string, but have not been successful in combining them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt 2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))

Thanks folks. I'm not quite sure if a query or a pivot table is really what
I want. It seems too manual. I use this data daily and roll it into weekly
and monthly reports. There are hundreds of products to sort. Anyway, a
couple of questions about Biff's formula. It works but does not consider the
date constraint nor the vendor "other". What is curious also is when putting
to double closed parentheses after the first statement, all the commas
separating the statements disappear. I'm testing in a workbook now that
contains the data so it looks a little simpler. Additionally, the
calculation counts more products than actually exist by changing the location
of parentheses.

I'm thinking the only way to get around this is to count all "Toy-" and
subtract the "Toy-ITA" which would result in the number of "Toy-nggf and
toy-vss" My latest calc is shown below. Any help is apprecaited. Thanks
again.

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}))--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2)--(LEFT($AA$2:$AA$251,5)="Other")

"keepITcool" wrote:


you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

Im trying to count the number of products in column B for only
those that contain the characters €œvss€� or €œnggf€� which
apprear at the end of the product name. The products are listed as
toy-vss, toy-nggf, and toy-ita. It appears that the formula below
works, but only looks at the €œvss€� query and ignores the
€œnggf€� query. Is there a way to combine the query so if
€œvss€� or €œnggf€� are in Column B, it will count those products
and give me the total number of occurences? Im thinking combining
the names in a string, but have not been successful in combining them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt 2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))

Hi!

Looks like you just have mismatched ( ).

Try this:

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))

Biff

"pomalley" wrote in message
...
Thanks folks. I'm not quite sure if a query or a pivot table is really
what
I want. It seems too manual. I use this data daily and roll it into
weekly
and monthly reports. There are hundreds of products to sort. Anyway, a
couple of questions about Biff's formula. It works but does not consider
the
date constraint nor the vendor "other". What is curious also is when
putting
to double closed parentheses after the first statement, all the commas
separating the statements disappear. I'm testing in a workbook now that
contains the data so it looks a little simpler. Additionally, the
calculation counts more products than actually exist by changing the
location
of parentheses.

I'm thinking the only way to get around this is to count all "Toy-" and
subtract the "Toy-ITA" which would result in the number of "Toy-nggf and
toy-vss" My latest calc is shown below. Any help is apprecaited. Thanks
again.

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}))--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2)--(LEFT($AA$2:$AA$251,5)="Other")

"keepITcool" wrote:


you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

I'm trying to count the number of products in column B for only
those that contain the characters "vss" or "nggf" which
apprear at the end of the product name. The products are listed as
toy-vss, toy-nggf, and toy-ita. It appears that the formula below
works, but only looks at the "vss" query and ignores the
"nggf" query. Is there a way to combine the query so if
"vss" or "nggf" are in Column B, it will count those products
and give me the total number of occurences? I'm thinking combining
the names in a string, but have not been successful in combining them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt 2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))

Your fomula:
=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))
Not having much luck with your formula. Im sending the formula I used per
your instructions, but get the #VALUE output.

My formula (I cut down my spreadsheet to test so some cell addresses have
been changed to protect the dataset.)
=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

Date Sold Product Units Sold Vendor
1/3/2005 Toy-ITA 138 Other-ITA
2/19/2005 Toy-ITA 145 Other-ITA
1/13/2005 Toy-NGGF 69 IT
2/9/2005 Toy-NGGF 35 Other
2/28/2005 Toy-NGGF 318 Other
3/4/2005 Toy-NGGF 150 Other
1/12/2005 Toy-VSS 98 IGS
3/12/2005 Toy-VSS 93 Other

Results:

Vendor=Other 1/1/2005 2/1/2005
3/1/2005
Total NGGF & VSS Sales #VALUE #VALUE
#VALUE
Total NGGF & VSS Units Sold #VALUE #VALUE
#VALUE
Total VSS Sales 0 0
1
Total VSS Units Sold 0 0
93
Total ITA Sales 1 1
0
Total ITA Units Sold 138 145
0

On the other hand, when entering the following formula, I get the correct
result using only one product criteria. I can substitute NGGF as well as
€“VSS and get the correct result.


=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)="-ITA"),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

To solve the problem, I created a small table to the far right of my
worksheet which works out the calcs for each €œtoy€�, then I take that sum and
put it into my table from which I create graphs. If there is a better more
efficient way to do this, I'd appreciate knowing. Thanks again for all your
help.




"Biff" wrote:

Hi!

Looks like you just have mismatched ( ).

Try this:

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))

Biff

"pomalley" wrote in message
...
Thanks folks. I'm not quite sure if a query or a pivot table is really
what
I want. It seems too manual. I use this data daily and roll it into
weekly
and monthly reports. There are hundreds of products to sort. Anyway, a
couple of questions about Biff's formula. It works but does not consider
the
date constraint nor the vendor "other". What is curious also is when
putting
to double closed parentheses after the first statement, all the commas
separating the statements disappear. I'm testing in a workbook now that
contains the data so it looks a little simpler. Additionally, the
calculation counts more products than actually exist by changing the
location
of parentheses.

I'm thinking the only way to get around this is to count all "Toy-" and
subtract the "Toy-ITA" which would result in the number of "Toy-nggf and
toy-vss" My latest calc is shown below. Any help is apprecaited. Thanks
again.

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}))--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2)--(LEFT($AA$2:$AA$251,5)="Other")

"keepITcool" wrote:


you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

I'm trying to count the number of products in column B for only
those that contain the characters "vss" or "nggf" which
apprear at the end of the product name. The products are listed as
toy-vss, toy-nggf, and toy-ita. It appears that the formula below
works, but only looks at the "vss" query and ignores the
"nggf" query. Is there a way to combine the query so if
"vss" or "nggf" are in Column B, it will count those products
and give me the total number of occurences? I'm thinking combining
the names in a string, but have not been successful in combining them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005 Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt 2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))

Ok, let's get this figured out, shall we?

Based on the table you posted.

In the following cells I entered:

A12 = other
B12 = 1/1/2005
C12 = 2/1/2005
D12 = 3/1/2005

Formula in B13 copied across to D13:

=SUMPRODUCT((MONTH($A2:$A9)=MONTH(B12))*((RIGHT($B 2:$B9,4)="-vss")+(RIGHT($B2:$B9,4)="nggf"))*(LEFT($D2:$D9,5)= $A12))

Returned the following results:

B13 = 0 no entries met the criteria for the month of January
C13 = 2 2 entries met the criteria for the month of February
D13 = 2 2 entries met the criteria for the month of March

I can send you the file if you'd like to see it.

Biff

"pomalley" wrote in message
...
Your fomula:
=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))
Not having much luck with your formula. I'm sending the formula I used
per
your instructions, but get the #VALUE output.

My formula (I cut down my spreadsheet to test so some cell addresses have
been changed to protect the dataset.)
=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

Date Sold Product Units Sold Vendor
1/3/2005 Toy-ITA 138 Other-ITA
2/19/2005 Toy-ITA 145 Other-ITA
1/13/2005 Toy-NGGF 69 IT
2/9/2005 Toy-NGGF 35 Other
2/28/2005 Toy-NGGF 318 Other
3/4/2005 Toy-NGGF 150 Other
1/12/2005 Toy-VSS 98 IGS
3/12/2005 Toy-VSS 93 Other

Results:

Vendor=Other 1/1/2005
2/1/2005
3/1/2005
Total NGGF & VSS Sales #VALUE #VALUE
#VALUE
Total NGGF & VSS Units Sold #VALUE #VALUE
#VALUE
Total VSS Sales 0 0
1
Total VSS Units Sold 0 0
93
Total ITA Sales 1
1
0
Total ITA Units Sold 138
145
0

On the other hand, when entering the following formula, I get the correct
result using only one product criteria. I can substitute NGGF as well as
-VSS and get the correct result.


=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)="-ITA"),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

To solve the problem, I created a small table to the far right of my
worksheet which works out the calcs for each "toy", then I take that sum
and
put it into my table from which I create graphs. If there is a better
more
efficient way to do this, I'd appreciate knowing. Thanks again for all
your
help.




"Biff" wrote:

Hi!

Looks like you just have mismatched ( ).

Try this:

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))

Biff

"pomalley" wrote in message
...
Thanks folks. I'm not quite sure if a query or a pivot table is really
what
I want. It seems too manual. I use this data daily and roll it into
weekly
and monthly reports. There are hundreds of products to sort. Anyway,
a
couple of questions about Biff's formula. It works but does not
consider
the
date constraint nor the vendor "other". What is curious also is when
putting
to double closed parentheses after the first statement, all the commas
separating the statements disappear. I'm testing in a workbook now
that
contains the data so it looks a little simpler. Additionally, the
calculation counts more products than actually exist by changing the
location
of parentheses.

I'm thinking the only way to get around this is to count all "Toy-" and
subtract the "Toy-ITA" which would result in the number of "Toy-nggf
and
toy-vss" My latest calc is shown below. Any help is apprecaited.
Thanks
again.

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}))--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2)--(LEFT($AA$2:$AA$251,5)="Other")

"keepITcool" wrote:


you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

I'm trying to count the number of products in column B for only
those that contain the characters "vss" or "nggf" which
apprear at the end of the product name. The products are listed as
toy-vss, toy-nggf, and toy-ita. It appears that the formula below
works, but only looks at the "vss" query and ignores the
"nggf" query. Is there a way to combine the query so if
"vss" or "nggf" are in Column B, it will count those products
and give me the total number of occurences? I'm thinking combining
the names in a string, but have not been successful in combining
them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005
Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005
Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt 2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))

Well, you are pretty brilliant. With this, I can see how I can fine tune
some other formulas I'm using. I can't thank you enough. You're terrific.

"Biff" wrote:

Ok, let's get this figured out, shall we?

Based on the table you posted.

In the following cells I entered:

A12 = other
B12 = 1/1/2005
C12 = 2/1/2005
D12 = 3/1/2005

Formula in B13 copied across to D13:

=SUMPRODUCT((MONTH($A2:$A9)=MONTH(B12))*((RIGHT($B 2:$B9,4)="-vss")+(RIGHT($B2:$B9,4)="nggf"))*(LEFT($D2:$D9,5)= $A12))

Returned the following results:

B13 = 0 no entries met the criteria for the month of January
C13 = 2 2 entries met the criteria for the month of February
D13 = 2 2 entries met the criteria for the month of March

I can send you the file if you'd like to see it.

Biff

"pomalley" wrote in message
...
Your fomula:
=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))
Not having much luck with your formula. I'm sending the formula I used
per
your instructions, but get the #VALUE output.

My formula (I cut down my spreadsheet to test so some cell addresses have
been changed to protect the dataset.)
=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

Date Sold Product Units Sold Vendor
1/3/2005 Toy-ITA 138 Other-ITA
2/19/2005 Toy-ITA 145 Other-ITA
1/13/2005 Toy-NGGF 69 IT
2/9/2005 Toy-NGGF 35 Other
2/28/2005 Toy-NGGF 318 Other
3/4/2005 Toy-NGGF 150 Other
1/12/2005 Toy-VSS 98 IGS
3/12/2005 Toy-VSS 93 Other

Results:

Vendor=Other 1/1/2005
2/1/2005
3/1/2005
Total NGGF & VSS Sales #VALUE #VALUE
#VALUE
Total NGGF & VSS Units Sold #VALUE #VALUE
#VALUE
Total VSS Sales 0 0
1
Total VSS Units Sold 0 0
93
Total ITA Sales 1
1
0
Total ITA Units Sold 138
145
0

On the other hand, when entering the following formula, I get the correct
result using only one product criteria. I can substitute NGGF as well as
-VSS and get the correct result.


=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)="-ITA"),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

To solve the problem, I created a small table to the far right of my
worksheet which works out the calcs for each "toy", then I take that sum
and
put it into my table from which I create graphs. If there is a better
more
efficient way to do this, I'd appreciate knowing. Thanks again for all
your
help.




"Biff" wrote:

Hi!

Looks like you just have mismatched ( ).

Try this:

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))

Biff

"pomalley" wrote in message
...
Thanks folks. I'm not quite sure if a query or a pivot table is really
what
I want. It seems too manual. I use this data daily and roll it into
weekly
and monthly reports. There are hundreds of products to sort. Anyway,
a
couple of questions about Biff's formula. It works but does not
consider
the
date constraint nor the vendor "other". What is curious also is when
putting
to double closed parentheses after the first statement, all the commas
separating the statements disappear. I'm testing in a workbook now
that
contains the data so it looks a little simpler. Additionally, the
calculation counts more products than actually exist by changing the
location
of parentheses.

I'm thinking the only way to get around this is to count all "Toy-" and
subtract the "Toy-ITA" which would result in the number of "Toy-nggf
and
toy-vss" My latest calc is shown below. Any help is apprecaited.
Thanks
again.

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}))--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2)--(LEFT($AA$2:$AA$251,5)="Other")

"keepITcool" wrote:


you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

I'm trying to count the number of products in column B for only
those that contain the characters "vss" or "nggf" which
apprear at the end of the product name. The products are listed as
toy-vss, toy-nggf, and toy-ita. It appears that the formula below
works, but only looks at the "vss" query and ignores the
"nggf" query. Is there a way to combine the query so if
"vss" or "nggf" are in Column B, it will count those products
and give me the total number of occurences? I'm thinking combining
the names in a string, but have not been successful in combining
them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005
Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005
Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt 2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))

Glad to help! Thanks for the feedback.

Biff

"pomalley" wrote in message
...
Well, you are pretty brilliant. With this, I can see how I can fine tune
some other formulas I'm using. I can't thank you enough. You're
terrific.

"Biff" wrote:

Ok, let's get this figured out, shall we?

Based on the table you posted.

In the following cells I entered:

A12 = other
B12 = 1/1/2005
C12 = 2/1/2005
D12 = 3/1/2005

Formula in B13 copied across to D13:

=SUMPRODUCT((MONTH($A2:$A9)=MONTH(B12))*((RIGHT($B 2:$B9,4)="-vss")+(RIGHT($B2:$B9,4)="nggf"))*(LEFT($D2:$D9,5)= $A12))

Returned the following results:

B13 = 0 no entries met the criteria for the month of January
C13 = 2 2 entries met the criteria for the month of February
D13 = 2 2 entries met the criteria for the month of March

I can send you the file if you'd like to see it.

Biff

"pomalley" wrote in message
...
Your fomula:
=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))
Not having much luck with your formula. I'm sending the formula I used
per
your instructions, but get the #VALUE output.

My formula (I cut down my spreadsheet to test so some cell addresses
have
been changed to protect the dataset.)
=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

Date Sold Product Units Sold Vendor
1/3/2005 Toy-ITA 138 Other-ITA
2/19/2005 Toy-ITA 145 Other-ITA
1/13/2005 Toy-NGGF 69 IT
2/9/2005 Toy-NGGF 35 Other
2/28/2005 Toy-NGGF 318 Other
3/4/2005 Toy-NGGF 150 Other
1/12/2005 Toy-VSS 98 IGS
3/12/2005 Toy-VSS 93 Other

Results:

Vendor=Other 1/1/2005
2/1/2005
3/1/2005
Total NGGF & VSS Sales #VALUE #VALUE
#VALUE
Total NGGF & VSS Units Sold #VALUE #VALUE
#VALUE
Total VSS Sales 0
0
1
Total VSS Units Sold 0
0
93
Total ITA Sales 1
1
0
Total ITA Units Sold 138
145
0

On the other hand, when entering the following formula, I get the
correct
result using only one product criteria. I can substitute NGGF as well
as
-VSS and get the correct result.


=SUMPRODUCT(--(RIGHT($B$2:$B$10,4)="-ITA"),--(DATE(YEAR($A$2:$A$10),MONTH($A$2:$A$10),1)=$G$1),--(LEFT($D$2:$D$10,5)="Other"))

To solve the problem, I created a small table to the far right of my
worksheet which works out the calcs for each "toy", then I take that
sum
and
put it into my table from which I create graphs. If there is a better
more
efficient way to do this, I'd appreciate knowing. Thanks again for all
your
help.




"Biff" wrote:

Hi!

Looks like you just have mismatched ( ).

Try this:

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}),--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2),--(LEFT($AA$2:$AA$251,5)="Other"))

Biff

"pomalley" wrote in message
...
Thanks folks. I'm not quite sure if a query or a pivot table is
really
what
I want. It seems too manual. I use this data daily and roll it
into
weekly
and monthly reports. There are hundreds of products to sort.
Anyway,
a
couple of questions about Biff's formula. It works but does not
consider
the
date constraint nor the vendor "other". What is curious also is
when
putting
to double closed parentheses after the first statement, all the
commas
separating the statements disappear. I'm testing in a workbook now
that
contains the data so it looks a little simpler. Additionally, the
calculation counts more products than actually exist by changing the
location
of parentheses.

I'm thinking the only way to get around this is to count all "Toy-"
and
subtract the "Toy-ITA" which would result in the number of "Toy-nggf
and
toy-vss" My latest calc is shown below. Any help is apprecaited.
Thanks
again.

=SUMPRODUCT(--(RIGHT($B$2:$B$251,4)={"-VSS","NGGF"}))--(DATE(YEAR($A$2:$A$251),MONTH($A$2:$A$251),1)=$AU$ 2)--(LEFT($AA$2:$AA$251,5)="Other")

"keepITcool" wrote:


you're working with an external file already.
why not keep that closed and use a query or a query based pivot?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


pomalley wrote :

I'm trying to count the number of products in column B for only
those that contain the characters "vss" or "nggf" which
apprear at the end of the product name. The products are listed
as
toy-vss, toy-nggf, and toy-ita. It appears that the formula
below
works, but only looks at the "vss" query and ignores the
"nggf" query. Is there a way to combine the query so if
"vss" or "nggf" are in Column B, it will count those products
and give me the total number of occurences? I'm thinking
combining
the names in a string, but have not been successful in combining
them.

=SUMPRODUCT
(--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005
Data'!$B$2:$B$251,3)="VSS"),
--IF((RIGHT('[TDS Wkly Rpt 2005.xls]2005
Data'!$B$2:$B$251,4)="NGGF"),
--(DATE(YEAR('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),MONTH('[TDS Wkly Rpt 2005.xls]2005
Data'!$A$2:$A$251),1)=$FX$3), --(LEFT('[TDS Wkly Rpt
2005.xls]2005
Data'!$AA$2:$AA$251,5)="Other"))))