Solved: SUM(IF with an OR statement produces incorrect results.

[JimS]

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

[Bob Phillips]

Try this

=SUM(IF(FREQUENCY(IF('data dump'!$A$2:$A$20="Commit",IF(('data dump'!$B$2:$B$20="Chicago")+('data dump'!$B$2:$B$20="Minnesota"),
IF('data dump'!$D$2:$D$20<>"",MATCH('data dump'!$D$2:$D$20,'data dump'!$D$2:$D$20,0)))),ROW('data dump'!$D$2:$D$20)-ROW($D$2)+1),1))

[JimS]

Perfect, Thanks...