CODEWITHSUNDEEP
vbaexcelexcel-formula
user3095761
user3095761

Reputation: 13

count between cells vba

I am stuck in a situation I cannot solve.

I have a list showing a customer and his order like illustrated below. Customers i column A and ordernumber in column B

Customer - Ordernumber

And so on for 100+ customers.

How do I count the number or orders by each customer and write it in e.g column C? In the example above John has made 5 orders and Pauline 3.

Upvotes: 1

Views: 142

Answers (3)

user2140173
user2140173

Reputation:

I'd do it like this

Sample data

enter image description here

sort column A by the name ( including column B )

enter image description here

in column C row 1 add a formula =COUNTIF(A:A,A1)

then in row B add this formula =IF(A2=A1,"",COUNTIF(A:A,A2))

then drag the second formula all the way own and the result should be

enter image description here

Upvotes: 2

ssarabando
ssarabando

Reputation: 3517

If you don't want to use a function you could also use the Subtotal command (in Office 2010 it's in the Data menu) and select the Count function in the "Use function" field and the Ordernumber in the "Add subtotal to" list.

For the Subtotal command to work you have to have columns labels. Note that this will insert new lines instead of putting the totals in a new column which may or not be acceptable to you.

See here for the Office online help regarding the command.

Upvotes: 0

jgridley
jgridley

Reputation: 131

You could just use a formula:

=COUNTIF(A:A,"John")

This would count the number of cells in the range A:A that match the string "John".

You can also reference a cell to match on, for example:

=COUNTIF(A:A,A1)

This would count the number of cells in column A that match cell A1 including A1.

Upvotes: 2

Related Questions