An example best illustrates my problem:

I have a long column of product codes, like the following

01051110
01051190
01051900
01059100
01059900
01060010
01060020
01060030
01060090
02062200
02062900
02063000
02064100
02064900
03011000
03019100
03019200
03019300


As you can see products start 01 then go to 02 then 03. In practice
this continues-04,05 etc until i come to 09, then it changes to 11, 12,
13,..19 then changes again to 21, 22, 23,..29. It does this until 91,
92,...99.

I wish to count the number of 2-digit products and present them in a
table. For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

Is there a quick way of doing this?


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

look in Help index for LEFT and COUNTIF

--
Don Guillett
SalesAid Software

"cj21" wrote in message
...

An example best illustrates my problem:

I have a long column of product codes, like the following

01051110
01051190
01051900
01059100
01059900
01060010
01060020
01060030
01060090
02062200
02062900
02063000
02064100
02064900
03011000
03019100
03019200
03019300


As you can see products start 01 then go to 02 then 03. In practice
this continues-04,05 etc until i come to 09, then it changes to 11, 12,
13,..19 then changes again to 21, 22, 23,..29. It does this until 91,
92,...99.

I wish to count the number of 2-digit products and present them in a
table. For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

Is there a quick way of doing this?


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

This has speeded things up, Thankyou.

I created a column with the formula: =LEFT(D2,2). This created a list
of the products. Then i put in the formula in my "counting table"
=COUNTIF(L2:L6088,"01").

This has worked but when i fill down the count formula, the next line
becomes: =COUNTIF(L3:L6089,"01"). I want it to read
=COUNTIF(L2:L6088,"02").

In otherwords I just want the "criteria" to change. Do you know how to
do this?

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

"cj21" wrote:
... Then i put in the formula in my "counting table"
=COUNTIF(L2:L6088,"01").

One way.. put instead in your starting cell, and copy down:
=COUNTIF($L$2:$L$6088,TEXT(ROW(A1),"00"))

Btw, you may also wish to try the SUMPRODUCT alternative
suggested in my earlier response
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Assuming source data in A1:A20
List the product codes in B1 down: 1,2,3, ...
Put in C1, and copy down:
=SUMPRODUCT(--(LEFT(TEXT($A$1:$A$20,"00000000"),2)=TEXT(B1,"00") ))
Adapt the range to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cj21" wrote in message
...

An example best illustrates my problem:

I have a long column of product codes, like the following

01051110
01051190
01051900
01059100
01059900
01060010
01060020
01060030
01060090
02062200
02062900
02063000
02064100
02064900
03011000
03019100
03019200
03019300


As you can see products start 01 then go to 02 then 03. In practice
this continues-04,05 etc until i come to 09, then it changes to 11, 12,
13,..19 then changes again to 21, 22, 23,..29. It does this until 91,
92,...99.

I wish to count the number of 2-digit products and present them in a
table. For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

Is there a quick way of doing this?


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

The results in B1:C3 would be, for the sample data as posted:

1 9
2 5
3 4

which tallies with:

... For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

except for the typo in: "... 4 products that begin 04" (04 should be 03)

The formula would work even if the numbers in B1:B3
were entered as text numbers: 01, 02, 03
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

Assuming the posted table is in A1:B72 (data from row2 to 72)

List the codes say, in D2 down: 1,2,3 ...

Put in E2, array-enter (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(--(LEFT(TEXT($A$2:$A$72,"00000000"),2)
=TEXT(D2,"00")),$B$2:$B$72))
Copy E2 down

Col E will return the average tariff for the product codes listed in col D
Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cj21" wrote in message
...

That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

I tried to do this latter approach for averages but it didn't work.

My product codes are in column D, their corresponding tariffs are in
column H and my list of 2-digit products ( that correspond to the
8-digit products e.g. 02134567 becomes 02) are in cloumn M.

I put in the following formula:

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$6088,"00000000"),2)
=TEXT(M2,"00")),$H$3:$H$6088))

This returns an average for product 01 as 24.80, when it should be 23,
what am i doing wrong. Also when i fill down the formula there are just
zeros in every column.

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

On Fri, 4 Nov 2005 09:07:43 -0600, cj21
wrote:


That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris

You can do this easily using a helper column along with either Pivot Table (my
preference) or SubTotals.

First set up a third helper column -- let's call it Type.

Formula: =INT(ProductCode/10^6)

That'll give you the first two digits of the Product Code.

For a Pivot Table, with the active cell someplace in the table, select
Data/PivotTable Finish

Drag Type to the Rows area
Drag Type to the Data area
Drag Tariff to the Data area

Right click on "sum of type"; Field Settings and select Count
Rename it as you wish and also select Number if you don't like the
default number format.

Right click on "sum of tariff"; Field Settings and select Average
Rename it as you wish and also select Number if you don't like the
default number format.

Finally, with the cursor in the Pivot Table, select from the top menu bar
Format/Auto Format and select an attractive format for your report.

=============================

For the Data/Subtotals wizard, you first have to ensure your data is sorted by
Type (i.e. first one or two digits).

Then select Data/Subtotals

At each change in Type
Use Function Count
Add Subtotal To (You can choose any field here; the difference will be
where it appears in the report)

Then again select Data/Subtotals
Ensure Replace Current Subtotals is DEselected

At each change in Type:
Use Function Average:
Add Subtotal to Tarriff

Then collapse the table using the buttons on the left side to display how you
want.

Rename the labels to your liking.


--ron