Conditional sums - counting unique

[E Halliday]

I have been searching in the groups for an answer but can't seem to find one
that fits my problem... so apologies if you have said this a few times before.

I have the following formula to calculate the unique records where items
match a specific criteria - but it takes forever to run (and near crashes
everytime!) - does anyone know of anything which would make this less memory
'hungry'? The spreadsheet is >40,000 rows.

={SUM(IF('Client data'!$AN$2:$AN$31533="Less than £100m",IF('Client
data'!$AI$2:$AI$31533="AUSTRIA",1/COUNTIF($B$2:B$31533,$B$2:B$31533),0),0))}

I've attempted a sumproduct - but it has been returning an error so I'm lost!
thanks in advance

 

[Bob Phillips]

SUMPRODUCT won't be any quicker, it uses the same principles.

You need to break it up.

See if this is any quicker.

In say N2, add

=AND('Client Data'!$AN2="Less than £100m",'Client Data'!$AI2="AUSTRIA"

and copy down to N31533

Then use

=SUM(IF($N$2:$N$31533,1/COUNTIF($B$2:$B$31533,$B$2:$B$31533)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Roger Govier]

Hi
Try
=SUMPRODUCT(('Client data'!$AN$2:$AN$31533="Less than £100m")*
('Client data'!$AI$2:$AI$31533="AUSTRIA")*
($B$2:B$31533<>""))

 

[CLR]

I use Jim Cone's fine commercial Add-in called XLCompanion for counting
"uniques", among many other things. If your are interested, it's available
at:

http://www.realezsites.com/bus/primitivesoftware/

Vaya con Dios,
Chuck, CABGx3

 

[E Halliday]

Hi,

Thanks Roger - but this is counting all - not just unique fields in column B?

Any other ideas?

 

[E Halliday]

Thanks Bob, but the criteria are just examples, and I need to pull out other
countries.... I think I need to put it in a database!

Thanks again

 

[Bernie Deitrick]

You should be careful with that formula - it doesn't really work.

With this table in A1:C5:

Value1 Value2 Name
Halliday Other Bernie
Halliday Excel Bernie
Halliday Excel Bernie
Halliday Excel Danny

This formula

{=SUM(IF(A2:A5="Halliday",IF(B2:B5="Excel",1/COUNTIF(C2:C5,C2:C5),0),0))}

returns 1.666667 not 2 because the 1/COUNTIF returns 3 and not 2 when it is doing the count for
Bernie. Over the course of thousands of records, you will get a very large error.

HTH,
Bernie
MS Excel MVP

 

[Ollie4]

i've got a workaround

sort the column you want to count uniques from and use a simple, in
this case efering to column AN)
formula cel AO3 (or else on row 3)
=IF(AND(AN2<>AN3;AN4<>AN3);1;0)
copy this over all rows
now sum column AO
check the first and last cel in the AN column

(possible you have to use , instead of ; )

if this works i can make a more permanent solution for you if you want
to

 

[Bernie Deitrick]

Put your crieria for column AN (the "Less than 100m") into row 1 starting in column B, and the
Austria criteria into column A starting in row 2. Then in B2, array enter

=SUM(N(FREQUENCY(IF(('Client Data'!$AN$2:$AN$31533=B$1)*('Client
Data'!$AI$2:$AI$31533=$A2),MATCH('Client Data'!$B$2:B$31533,'Client
Data'!$B$2:B$31533,0)),MATCH('Client Data'!$B$2:B$31533,'Client Data'!$B$2:B$31533,0))>0))

and copy to match your list of criteria, say to H10.

HTH,
Bernie
MS Excel MVP

 

[E Halliday]

Thanks Bernie - I had actually just noticed on some v. dodgy looking
results...

 

[Ollie4]

have you tried this sumproduct variant?

sumproduct((condition1)*(condition2)*(conditionN)*(1)) ?

By giving (1) instead of (range) will count every unique hit on your
conditions.


example:
sumproduct((Client Data'!$AN$2:$AN$31533=B$1)*('Client Data'!$AI$2:$AI
$31533=$A2)*(1))

I cant see your data, you can add all conditions you want this way

 

[Bernie Deitrick]

Ollie,

That doesn't count unique values - just the total count that meet the
combination of conditions.... a useful technique, but not what the OP is
looking for.

HTH,
Bernie
MS Excel MVP

 

[Ollie4]

Ollie,

That doesn't count unique values - just the total count that meet the
combination of conditions.... a useful technique, but not what the OP is
looking for.

true but with a little trick you can use it for unique values. if the
unique values are in colomn B than put on every row in an other colomn
(i.e colomn Y on cel Y2 ):

if(sumproduct(($B$2:$B$31533=B2)*(1))=1,1,0)

This will count the times the value on the row will appear in the
total colomn. If the value is > 1 the value will not be unique. If the
value is 1, it will be unique and the if statement will return a 1.
now just sum colomn Y and you'll have the number of unique
values. ;-)

copy the formula over colomn Y
on cel Y3: if(sumproduct(($B$2:$B$31533=B3)*(1))=1,1,0) and so on
now sum(Y2:Y31533) and you have a count of all unique values.

 

[Roger Govier]

Sorry, missed the unique part.

 

[Bernie Deitrick]

No, that formula will not find the count of unique values where the other
columns match specific criteria. That will find values that are "one-sies",
independent of the criteria. But the general idea of "unique" is if a value
appears 1000 times, it counts as one in the count of unique values.

Your column of formulas would need to be modified to

=if(sumproduct(($Range1 = criteria1)*(Range2 =
criteria2)*($B$2:$B2=B2)*(1))=1,1,0)

in a cell in row 2, copied down to match.

But a single cell formula can do all that, without using 37000 cells.... see
my other posts.

Bernie