Subtotal in new column

P

Pete Provencher

Using Excel 2000:

I know that you can use subtotal from the main menu but what I would like it
to do is put the subtotal number in a new column. What I have is a lot of
records with a company name as a field.. I would like it to count the
duplicate company names and on the first row of a particular company to put
the total times the compay name appears in a new column.

For example:

Company name Count

A 2
A
B 1
C 3
C
C
.......and so forth


Pete provencher
 
T

Trevor Shuttleworth

Pete

assuming the data is in Column A and there is a header in Cell A1, put this
formula in a cell in Row 2

=IF(A1=A2,"",COUNTIF(A:A,A2))

and drag down.

Regards

Trevor
 
D

David McRitchie

Hi Pete,
Since you asked for a total you might want to modify
your request and Trevor's suggestion from

Company, Count, B1: Count
A 2 B2: =IF(A1=A2,"",COUNTIF(A:A,A2))
A B3: =IF(A2=A3,"",COUNTIF(A:A,A3))
B 1 B4: =IF(A3=A4,"",COUNTIF(A:A,A4))
C 3 B5: =IF(A4=A5,"",COUNTIF(A:A,A5))
C B6: =IF(A5=A6,"",COUNTIF(A:A,A6))
C B7: =IF(A6=A7,"",COUNTIF(A:A,A7))
6 B8: =SUM(OFFSET(B1,1,0):OFFSET(B8,-1,0))

To a solution that subtracts one from the counts so the total is the
number of duplicate.

Company, count, B1: Count
A 1 B2: =IF(A1=A2,"",COUNTIF(A:A,A2)-1)
A B3: =IF(A2=A3,"",COUNTIF(A:A,A3)-1)
B 0 B4: =IF(A3=A4,"",COUNTIF(A:A,A4)-1)
C 2 B5: =IF(A4=A5,"",COUNTIF(A:A,A5)-1)
C B6: =IF(A5=A6,"",COUNTIF(A:A,A6)-1)
C B7: =IF(A6=A7,"",COUNTIF(A:A,A7)-1)
3 B8: =SUM(OFFSET(B1,1,0):OFFSET(B8,-1,0))
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Trevor Shuttleworth said:
Pete

assuming the data is in Column A and there is a header in Cell A1, put this
formula in a cell in Row 2 [i.e. cell B2]

=IF(A1=A2,"",COUNTIF(A:A,A2))

and drag down [with the fill handle].

Regards

Trevor


Pete Provencher said:
Using Excel 2000:

I know that you can use subtotal from the main menu but what I would like it
to do is put the subtotal number in a new column. What I have is a lot of
records with a company name as a field.. I would like it to count the
duplicate company names and on the first row of a particular company to put
the total times the compay name appears in a new column.

For example:

Company name Count

A 2
A
B 1
C 3
C
C
......and so forth


Pete provencher
Click to expand...
Click to expand...
 
Top