Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting... | Excel Worksheet Functions | |||
counting Multiple answers in 1 cell + column | Excel Discussion (Misc queries) | |||
counting Multiple answers in 1 cell + column | Excel Discussion (Misc queries) | |||
Back Dating (Counting Backwards) | Excel Discussion (Misc queries) | |||
Counting days worked | Excel Worksheet Functions |