# Thread: Count Unique Locations (Excel 97-SR2)

1. ## Count Unique Locations (Excel 97-SR2)

I'm trying to count the number of new(unique) locations in a table.. I'm stuck... Can someone assist please?

The formula I have lashed up is... =COUNT(COUNTIF(\$C\$3:C\$9,"=0"),COUNTIF(D\$3\$9,"=64")) Which is patently wrong!

Attached spreadsheet... is an example of what I'm trying to do?

Regards
Peter

2. ## Re: Count Unique Locations (Excel 97-SR2)

I'm sorry, I don't understand what you mean by "new (unique) locations". Could you try to explain it, and what you expect the result to be in the spreadsheet you attached?

3. ## Re: Count Unique Locations (Excel 97-SR2)

I second Hans' request and also make a suggestion:
If you put the formula in C12:
=COUNTIF(C\$3:C\$9,\$B12)

You can copy it from C12:E15 without having to do all the editing and creating new formulas for each row.

As per your formula, it will always yield 2, since you are counting 2 things (numbers):
COUNTIF(\$C\$3:C\$9,"=0") and COUNTIF(D\$3\$9,"=64") each is a value (no matter what is in the cells) and you told excel to count them.

Just a guess: are you trying for this ARRAY (confirm with ctrl-alt-enter) in D17 ( count the values of each when col c = 0) copy from D1820

=SUM(IF((C\$3:C\$9=0)*(D\$3\$9=\$B17),1))

And this array for E17 (copy to E18: E20):
=SUM(IF((C\$3:C\$9=0)*(D\$3\$9=0)*(E\$3:E\$9=\$B17),1))

Count the values in E when both C&D have 0 or perhaps:
=SUM(IF(((C\$3:C\$9=0)+(D\$3\$9=0))*(E\$3:E\$9=\$B17),1))

Count the values in col B when EITHER C or D is 0

Steve

4. ## Re: Count Unique Locations (Excel 97-SR2)

Hi Hans/Steve

Apologies for my poor explanation.... Steve manged to understand what I was mumbling about:

<font color=red>Just a guess: are you trying for this ARRAY (confirm with ctrl-alt-enter) in D17 ( count the values of each when col c = 0) copy from D1820

=SUM(IF((C\$3:C\$9=0)*(D\$3\$9=\$B17),1))

And this array for E17 (copy to E18: E20):
=SUM(IF((C\$3:C\$9=0)*(D\$3\$9=0)*(E\$3:E\$9=\$B17),1))

Count the values in E when both C&D have 0 <font color=black>

Updated spreadsheet attached. Essentially what I am trying to do is understand how many NEW locations each additional user brings. Steve's formulas have answered my query.

Thanks Steve! (and Hans)

Regards
Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•