Hello everyone - i have been working with this for a while now, and I haven't made the kind of progress that I would like. I'm in Excel 2007, and i'm looking for a way to (using cell logic) to count a list of unique values in a column, based on a column with a month key (like "200702" means Feb 2007). I want to stick with cell logic, so array equations are fine. So let me pose my question to you:

Let's say I work at a car repair center. When I'm documenting my work, I write down the license plate number (which can be ANY sequence of letters and numbers, and CANNOT be predicted) and the number of things that I work on in the car (represented by each row), and the month I work on it in. Some cars may only have one problem, while others may have two or three (so that would lead to duplicate rows with the same license plate number). So the question is - In January 2007 (200701), how many cars did I work on? It's very easy to count how many total issues that we dealt with, but not unique license plate numbers.

RTS1338 200701

RTS1338 200701

RTS1338 200701

ARE9296 200701

177J14R 200701

8172RRT 200702

7629PLU 200701

7629PLU 200701

177GHY2 200702

So the correct answer would be 4 cars, with 7 issues in 200701. So far, I have an array equation that finds the total unique values, but not with the additional criteria in months applied to it.

{=SUM(1/IF(A2:A14="",1,(COUNTIF(A2:A14,A2:A14)))-COUNTBLANK(A2:$A14))}

And keep in mind, my data set is ~50,000 rows - so I can't guess on what the license plate number will be. I have to have Excel see it and say, "OK - given this number 200701, I need to look at the cells next to these values and total the unique values that I find".

Any help would be greatly appreciated. I wish FREQUENCY would work, but it seems to dislike text.

Let's say I work at a car repair center. When I'm documenting my work, I write down the license plate number (which can be ANY sequence of letters and numbers, and CANNOT be predicted) and the number of things that I work on in the car (represented by each row), and the month I work on it in. Some cars may only have one problem, while others may have two or three (so that would lead to duplicate rows with the same license plate number). So the question is - In January 2007 (200701), how many cars did I work on? It's very easy to count how many total issues that we dealt with, but not unique license plate numbers.

RTS1338 200701

RTS1338 200701

RTS1338 200701

ARE9296 200701

177J14R 200701

8172RRT 200702

7629PLU 200701

7629PLU 200701

177GHY2 200702

So the correct answer would be 4 cars, with 7 issues in 200701. So far, I have an array equation that finds the total unique values, but not with the additional criteria in months applied to it.

{=SUM(1/IF(A2:A14="",1,(COUNTIF(A2:A14,A2:A14)))-COUNTBLANK(A2:$A14))}

And keep in mind, my data set is ~50,000 rows - so I can't guess on what the license plate number will be. I have to have Excel see it and say, "OK - given this number 200701, I need to look at the cells next to these values and total the unique values that I find".

Any help would be greatly appreciated. I wish FREQUENCY would work, but it seems to dislike text.

Last edited: