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

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)



"E Halliday" wrote in message
...
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

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

--
Regards
Roger Govier

"E Halliday" wrote in message
...
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

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" wrote:

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

Hi,

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

Any other ideas?


"Roger Govier" wrote:

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

--
Regards
Roger Govier

"E Halliday" wrote in message
...
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

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

"Bob Phillips" wrote:

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)



"E Halliday" wrote in message
...
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

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


"E Halliday" wrote in message
...
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

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

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" wrote in message
...
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

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


"Bernie Deitrick" wrote:

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


"E Halliday" wrote in message
...
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

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

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" wrote in message
...
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

On 5 feb, 19:37, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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.

Sorry, missed the unique part.

--
Regards
Roger Govier

"E Halliday" wrote in message
...
Hi,

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

Any other ideas?


"Roger Govier" wrote:

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

--
Regards
Roger Govier

"E Halliday" wrote in message
...
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

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



"Ollie4" wrote in message
...
On 5 feb, 19:37, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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.