Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Kenny Wisdom]

Let’s say I’ve got two columns for arguments sake. In column A there is always TEXT (actually a reference number with Alpha/Numeric) and in column B there are blanks and non-blanks of text.

I want to do a couple of things. Firstly, I want to count everything in column A, regardless of what’s in column B. However, entries in column A may be repeated according to what is in column B – so I need to use advanced filter and ask column A to display “Unique Records” only – I trust that this is correctly returning only one example of each.

Using COUNTA isn’t sophisticated enough because that will count hidden cells and I don’t want those included.
So, I am using this convoluted formula, which seems crazy. I just want to count VISIBLE ROWS as unique entries according to whether I expand or contract the rows!

Formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A*,ROW(A*:A*)-ROW(A*),0)),--(A*:A*<>””))

A* denotes Column A, (row number) e.g. A2:A500.

That’s one hell of a formula just to get Excel to count 1,2,3,4...! But it does actually count something!
The second problem occurs when I want to count only the number of entries in column A with non-blanks in column B – if I use the AUTOFILTER on column B it will change that column, but the rule for UNIQUE RECORDS on column A is ignored, so I might get duplicates returning a false count.  That and the formula at the foot of column A disappears. Help!

What’s the solution? How can I count unique records only, including blanks and then non-blanks without getting a false result and the formula must remain in place, recalculating every time the column expands or contracts? It must be a formula that counts TEXT entries, not values.

[McMullet]

I'm not 100% sure I understand the problem, but here's how I do what I think you're trying to do...

First, make a third column with this equation: =SUM(IF(A1=$A$1:$A$100,1,0)) and make it an array formula before filling down (i.e., press ctrl-shift-enter instead of enter). This should tell you how many instances of the given entry there are, so for example if the entry in A54 is copied 3 more times, C54 will say 3.

Example:

A .. 3
C .. 1
A .. 3
B .. 2
B .. 2
A .. 3
D .. 1

Then you make another array formula: =SUM(IF(1=A1:A100,1,IF(0=A1:A100,0,1/A1:A100)))

That will count the number of unique entries.

It's not the neatest way, but it works.

[Kenny Wisdom]

mm..I tried the first array formula and it just gave a result of "1" all the way down, even though I know there are duplicates.

I'll try and show you the problem:

H00003   W*** ***
H00003   V*** ***
H00009   
H00013   S*** ***
H00013   F*** ***
H00017   Y*** ***
H00019   
H00025   Q*** ***
H00028   P*** ***
H00028   G*** ***
H00041   
H00044   N*** ***
H00045   
H00047   
H00051   
H00052   
H00053   
H00055   Y*** ***
H00058   

In this example you will see some duplicates in column A e.g. H00003, H00013, H00028. This is quite correct because they have more than one TEXT item in column B.

However what I would want to be able to do is count every unique record in column A - which in this case would be 16 records.

Then I would want to know of those unique records how many have at least one TEXT item in column B. I just counted and would expect to see a result of 7 if this was working properly - it shouldn't count anything more than once - I just need to know yes, H00003 does have a corresponding entry in column B, and not that it has 2 entries.

It's doing my head in. Should be so simple but no, they haven't invented computers I can just talk to yet.

[McMullet]

I tried with your data and it works - are you sure you entered the formulae properly?

Paste the formula in, correct the cell reference and hit ctrl-shift-enter. Any time you edit the cell you have to hit ctrl-shift-enter again or it won't recognise it as an array formula. You can tell because it has {curly brackets} around it when you select the cell.

Not sure about the second part... that does sound trickier.

[Haushinka]

<a href="http://tinypic.com" target="_blank"><img src="http://i34.tinypic.com/2euismt.gif" border="0" alt="Image and video hosting by TinyPic">[/url]

[McMullet]

That too.

[Kenny Wisdom]

Thanks for your help, I'll try again next week. If I don't go for option 2. Thanks, Haushy.

[yosmark]

Excel is for pussies.

[Kenny Wisdom]

Thank you, Yossy.

[McMullet]

Excel is for pussies.

Well, naturally the manliest approach is to solve these problems using MS Paint.

Anyway, sticking to the pussy approach... The following works, even if it is a bit of a sledgehammer technique and needs more columns.

A	B	C	D	E
H00003	W*** ***	=SUM(IF(A1=$A$1:$A$19,1,0))	=IF(B1=0,,A1)	=SUM(IF(D1=$D$1:$D$19,1,0))

The equations in C and E need to be array formulae.

To get the number of unique entries in col. A:

=SUM(IF(1=C1:C19,1,IF(0=C1:C19,0,1/C1:C19)))

(array again)

The number of unique entries in A that have anything in B:

=SUM(IF(1=E1:E19,1,IF(0=E1:E19,0,1/E1:E19)))-1

(array)

When I do this I get 16 for the first formula and 7 for the second.

[Kenny Wisdom]

I'm thinking I need to take a crash course in pivot tables.

[McMullet]

Never used them, but that appears to be the sort of thing to do the job.

The array formulae will work OK if you're only interested in one thing, but it's a MacGyver duct tape job so it's probably better to learn the proper way.

[Kenny Wisdom]

Yep, I think you're right. I was looking at one load of data and of course that raised umpteen other questions which then I decided I needed to dig into, which meant loads of filters and scenarios and what ifs.

Thanks though - only by looking at the problem from the array formula point of view did I make that decision.