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