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