Counting!

R

Rob Gould

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
 
J

Jason Morin

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
 
A

Alan Beban

Rob said:
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
Click to expand...
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
 
Top