Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
pomalley
 
Posts: n/a
Default Combine query to count products with similar names

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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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



  #3   Report Post  
keepITcool
 
Posts: n/a
Default


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

  #4   Report Post  
pomalley
 
Posts: n/a
Default

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


  #5   Report Post  
Biff
 
Posts: n/a
Default

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






  #6   Report Post  
pomalley
 
Posts: n/a
Default

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




  #7   Report Post  
Biff
 
Posts: n/a
Default

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






  #8   Report Post  
pomalley
 
Posts: n/a
Default

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







  #9   Report Post  
Biff
 
Posts: n/a
Default

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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I count similar word in raw? Pinakeen Patel Excel Discussion (Misc queries) 1 March 9th 05 09:28 PM
Formula for a count of names? Alesha Excel Discussion (Misc queries) 2 February 23rd 05 01:12 AM
Combine names seperated by comma bbc1 Excel Discussion (Misc queries) 3 February 13th 05 08:55 PM
how can I count distinct names in an excel list? RPC@Frito Excel Discussion (Misc queries) 5 February 3rd 05 10:12 PM
Count Unique Names in list w/ Additional Criteria? Nodak Excel Worksheet Functions 1 January 26th 05 12:15 AM


All times are GMT +1. The time now is 04:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"