Counting!
 

Hi all,

I have some data which has been imported into Excel and is thus not
laid out in the best way. So what I get is Customer number in A1,
Customer name in B1. Below the Cus number are blank cells until the
next customer. Below B1 are dates on which sales where made to the
customer, until the next Customer name is reached. There are obviously
different numbers of rows of data for each customer.

I want to be able to count the number of dates under each customer.
For example I wish to know that ABC Customer purchased on 10 occasions
during a month.

Is there any way to do this quickly with the data laid out as it is? I
would prefer the result to be in the cell next to the customer name,
but elsewhere is fine if it can be done.

Thanks

Rob

First I would fill in the cust IDs in col. A:

1. Select the range in col. A, from A1 to the last row.
2. Press F5 Special. Click "Blank". Click OK.
3. Press "=", click cell A1, and press <Ctrl<Enter.

This will make it easier to count. For example, to count
the number of dates for the month of March for
customer "XYZ", use:

=SUM(N(IF(ISNUMBER(B1:B20)*(A1:A20="XYZ"),MONTH(B1 :B20))
=3))

where the "3" represents March. Apr=4,May=5,etc. This is
an array formula, so you must press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi all,

I have some data which has been imported into Excel and
is thus not
laid out in the best way. So what I get is Customer
number in A1,
Customer name in B1. Below the Cus number are blank
cells until the
next customer. Below B1 are dates on which sales where
made to the
customer, until the next Customer name is reached.
There are obviously
different numbers of rows of data for each customer.

I want to be able to count the number of dates under
each customer.
For example I wish to know that ABC Customer purchased
on 10 occasions
during a month.

Is there any way to do this quickly with the data laid
out as it is? I
would prefer the result to be in the cell next to the
customer name,
but elsewhere is fine if it can be done.

Thanks

Rob

.

Rob Gould wrote:
Hi all,

I have some data which has been imported into Excel and is thus not
laid out in the best way. So what I get is Customer number in A1,
Customer name in B1. Below the Cus number are blank cells until the
next customer. Below B1 are dates on which sales where made to the
customer, until the next Customer name is reached. There are obviously
different numbers of rows of data for each customer.

I want to be able to count the number of dates under each customer.
For example I wish to know that ABC Customer purchased on 10 occasions
during a month.

Is there any way to do this quickly with the data laid out as it is? I
would prefer the result to be in the cell next to the customer name,
but elsewhere is fine if it can be done.

Thanks

Rob

In an available column, say Column J for this illustration, in J1 enter

=IF(AND(ISBLANK(A1),NOT(ISBLANK(B1))),OFFSET(J1,-1,0),A1) and fill down
as far as required;

then in C1 enter

=IF(NOT(A1=""),COUNTIF(J:J,J1)-1,"") and fill down as far as required.

This assumes that you do not thereafter insert rows except by importing
the whole range of new data.

Alan Beban