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