Count only unique ID's within sumproduct

[john434]

G'day all,

I'm attempting to summarise a large dataset with formulae as opposed to a pivot table. I would, of course, usually use a pivot table, but this issue is only part of the overall task which involves me updating a table on a monthly basis using VBA.

Anyhow, i'm currently at the testing stage and i want to make sure that my calculations are correct. This is where i am so far.

My dataset:
Person_ID------Service-----Group------start------end_date
01245----------Res---------A----------1/7/08-----3/9/08
085699---------Nurs--------A----------5/2/98-----5/7/07
01245----------Res---------A----------1/6/08-----3/9/08
085699---------Per---------A----------11/7/05----5/12/07
65895254-------Res---------B----------5/12/06----
897516---------Nurs--------B----------12/6/97----13/5/08
798585---------Res---------B----------7/6/05-----12/7/08
0187985--------Res---------A----------8/7/07-----13/9/08
etc... for some 50,000+ rows

I have created some named range thus:
Person_ID = ID
Service = Service
Group = group
Start = Start
End_date = end

and written some sumproduct formula's like this
=sumproduct((Service="Res")*(group="A")*(start<=15/08/2008)*(end>=15/08/2008))

This formula gives me an answer of "Current" records that meet the criteria in the sumproduct, in this case 3!

However, what i would really like to to discount where ID's are duplicated. These duplicates are not duplicate records, as there are further columns to the dataset, but i want to count people not records.

What additional arguements on "ID" would i have to add to my sumproduct to achieve this? The result i would like is 2 with the records of Person_ID 01245 only being counted once.

Please excuse the long post, i just wanted to be as clear as possible.

Many thanks in advance for your help!

Cheers

 

[Andrzejek]

I assume you are in Excel.

Just to count your Person_ID, could you just use Data - Filter... - Advanced Filter, Copy to Another Location option and check 'Unique Records Only' chackbox? This will give you unique records.

Just an idea....

Have fun.

---- Andy

 

[john434]

Thanks for your reply Andy.

Yes, i am indeed using excel. Typical that i have included as much info as possible and neglected to mention what program i'm using.

What i'm really after is to count the records where the criteria in my SUMPRODUCT are met AND each Person_ID is only counted once.

 

[GlennUK]

Hi John,

I've had a quick think, and the formula that I came up with is quite horrendous ... an array formula ( entered using Ctrl-Shift-Enter ):

=COUNT(1/FREQUENCY(IF(Service="Res",IF(Group="A",IF(start<=DATEVALUE("15/8/2008"),IF(end>=DATEVALUE("15/8/2008"),IF(Person_ID<>"",Person_ID))))),IF(Service="Res",IF(Group="A",IF(start<=DATEVALUE("15/8/2008"),IF(end>=DATEVALUE("15/8/2008"),IF(Person_ID<>"",Person_ID)))))))

If I have time this afternoon I'll see if I can come up with a better formula.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[john434]

Cheers Glenn,

It certainly appear to give me the right results although, like you say, it's a bit of a beast.

Food for thought though, do you not think it's possible within SUMPRODUCT?

Cheers again

 

[john434]

Hi all, unfortunately this solution isn't quite what i was after.

I'm looking at the possibly of using a helper column in conjunction with the SUMPRODUCT, in order to get the results i am after.

Any ideas?

 

[GlennUK]

Use a helper column like:

=IF(AND((Service="Res"),(Group="A"),(start<=DATEVALUE("15/08/2008")),(end>=DATEVALUE("15/08/2008")),COUNTIF(F1:$F$1,A2)=0),A2,"")

to get unique results.

and use

=COUNT(F2:F9)

to count the results.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Hi,

I find it interesting when a requirement is stated like...

I'm building a cabinet, but I can't use a joiner. I would normally use a joiner, but I'm going to have to just stap it together with something of lesser quality and with more effort required.

"I would, of course, usually use a pivot table, but this issue is only part of the overall task which involves me updating a table on a monthly basis using VBA."

That statement does not, in ANY way, preclude the possibility of using a PivotTable. I update tables daily, weekly, monthly, using VBA AND I use PivotTables when it makes the most sense to summarize data.

Please explain the reason that a PivotTable cannot be used.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]