JimS
07-27-2010, 08:40 AM
I have a large data sheet and want to count the “unique records” using the following formula:
{=SUM(IF(FREQUENCY(IF('data dump'!$A$2:$A$2000="Commit",IF(OR('data dump'!$B$2:$B$2000="Chicago",'data dump'!$B$2:$B$2000="Minnesota"),IF('data dump'!$D$2:$D$2000<>"",MATCH('data dump'!$D$2:$D$2000,'data dump'!$D$2:$D$2000,0)))),ROW('data dump'!$D$2:$D$2000)-ROW($D$2)+1),1))}
The formula works if I remove the OR part (OR('data dump'!$B$2:$B$2000="Chicago",). I need to test for 2 different values in B2:B2000.
I tried to use a Sumproduct formula but couldn’t make it work either.
Any ideas why this formula does not work?
Thanks...
JimS
{=SUM(IF(FREQUENCY(IF('data dump'!$A$2:$A$2000="Commit",IF(OR('data dump'!$B$2:$B$2000="Chicago",'data dump'!$B$2:$B$2000="Minnesota"),IF('data dump'!$D$2:$D$2000<>"",MATCH('data dump'!$D$2:$D$2000,'data dump'!$D$2:$D$2000,0)))),ROW('data dump'!$D$2:$D$2000)-ROW($D$2)+1),1))}
The formula works if I remove the OR part (OR('data dump'!$B$2:$B$2000="Chicago",). I need to test for 2 different values in B2:B2000.
I tried to use a Sumproduct formula but couldn’t make it work either.
Any ideas why this formula does not work?
Thanks...
JimS