Counting unique values

[amcintosh]

Hi all

I'm trying to setup a countif formula, but I want it only count if it's a unique match in the previous column...

for example: column A is used for category; and column B is what I want to count, but I would like it say: only 1 B for C1; Only 1 C for C1; Only 1 C for C2... etc (i've published an example below)

The data itself going in to a pivot table by Col1 and then Col2 as row headers with Sum of Col3.

What would be the best way to go about this?

Cheers

Example:

<table style="border-collapse: collapse; table-layout: fixed; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><tbody><tr style="height: 15pt;" height="20"><td class="xl6418278" style="height: 15pt; width: 48pt;" height="20" width="64">Col1</td> <td class="xl6418278" style="border-left: medium none; width: 48pt;" width="64">Col2</td> <td class="xl6418278" style="border-left: medium none; width: 48pt;" width="64">Col3</td> <td class="xl6418278" style="border-left: medium none; width: 48pt;" width="64">Count</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">56</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C3</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C4</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C5</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C5</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">89</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C5</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C5</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl6418278" style="height: 15pt; border-top: medium none;" height="20">C5</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl6418278" style="border-top: medium none; border-left: medium none;"> </td></tr></tbody></table>

 

[Zakkaroo]

Apologies if i have mis-understood the query, but is it not possible to avoid formula's by using the Data - Filter - Advanced Filter and select the option 'Unique Records Only' ?

 

[amcintosh]

Thx for the reply -
I wrote the message late last night so my initial query probably wasn't clear

My goal is to try to count of how many rows within a given section of a pivot table; however, because it's using something similar as my example data in the source data, which causes the counting function to return how many times it appears in the source data, which is not what I want to do.

I can always do it manually, but the problem is that the data going to get updated on a 2 day basis, which could cause the pivot table to expand

Using my source data in the opening post

This is my pivot table - however I want it returning 1 in the Count of Col2, so A total would = 4

<table style="border-collapse: collapse; width: 205pt;" border="0" cellpadding="0" cellspacing="0" width="272"><col style="width: 60pt;" width="80"> <col style="width: 17pt;" width="22"> <col style="width: 60pt;" width="80"> <col style="width: 68pt;" width="90"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 60pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); background: none repeat scroll 0% 0% rgb(219, 229, 241);" height="20" width="80">
</td> <td style="width: 17pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); background: none repeat scroll 0% 0% rgb(219, 229, 241);" width="22">
</td> <td style="width: 60pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); background: none repeat scroll 0% 0% rgb(219, 229, 241);" width="80">Sum of Col3</td> <td style="width: 68pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); background: none repeat scroll 0% 0% rgb(219, 229, 241);" width="90">Count of Col2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">A</td> <td>C1</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">
</td> <td>C2</td> <td align="right">10</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">
</td> <td>C3</td> <td align="right">8</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215);" height="20">
</td> <td>C5</td> <td align="right">11</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(79, 129, 189);" height="20">A Total</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;">
</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;" align="right">30</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;" align="right">7</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">B</td> <td>C1</td> <td align="right">7</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215);" height="20">
</td> <td>C3</td> <td align="right">11</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(79, 129, 189);" height="20">B Total</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;">
</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;" align="right">18</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;" align="right">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">C</td> <td>C1</td> <td align="right">4</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">
</td> <td>C2</td> <td align="right">9</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">
</td> <td>C3</td> <td align="right">71</td> <td align="right">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20">
</td> <td>C4</td> <td align="right">2</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215);" height="20">
</td> <td>C5</td> <td align="right">93</td> <td align="right">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(79, 129, 189);" height="20">C Total</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;">
</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;" align="right">179</td> <td style="font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; border-width: 0.5pt medium; border-style: solid none; border-color: rgb(79, 129, 189) -moz-use-text-color;" align="right">11</td> </tr> </tbody></table>