unique count with criteria in a table

[tsheets]

Hi all,

I have been struggling with this situation and am hoping someone can help.

I have found solutions that almost get me there, but, nothing fully fits my situation.

Here is some example data:

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]site[/TD]
[TD="width: 64"]org[/TD]
[TD="width: 64"]count org[/TD]
[TD="width: 64"]category[/TD]
[/TR]
[TR]
[TD]site1[/TD]
[TD]org1[/TD]
[TD][/TD]
[TD]cat1[/TD]
[/TR]
[TR]
[TD]site2[/TD]
[TD]org2[/TD]
[TD]org2[/TD]
[TD]cat2[/TD]
[/TR]
[TR]
[TD]site3[/TD]
[TD]org1[/TD]
[TD][/TD]
[TD]cat3[/TD]
[/TR]
[TR]
[TD]site4[/TD]
[TD]org3[/TD]
[TD]org3[/TD]
[TD]cat4[/TD]
[/TR]
[TR]
[TD]site5[/TD]
[TD]org3[/TD]
[TD]org3[/TD]
[TD]cat4[/TD]
[/TR]
[TR]
[TD]site6[/TD]
[TD]org4[/TD]
[TD][/TD]
[TD]cat1[/TD]
[/TR]
[TR]
[TD]site7[/TD]
[TD]org1[/TD]
[TD][/TD]
[TD]cat1[/TD]
[/TR]
[TR]
[TD]site8[/TD]
[TD]org4[/TD]
[TD]org4[/TD]
[TD]cat1[/TD]
[/TR]
</tbody>[/TABLE]

The situation:

data is in a table that is updated regularly through connection to oledb - call it 'table1' on Sheet1

need unique count of 'count org' only if category is 1 or 4

count org has blanks as indicated above, but, category does not contain blanks.

real data has thousands of rows and many categories, I want to exclude several categories (nested if or multiple logic tests in formula would be difficult).

I can (and tried to) put categories to count in separate criteria list as the categories rarely change. Single criteria would be much easier, but, I have a bunch.

I am looking for a single cell solution (dashboard type report), but, helper columns may be possible (the count org column is a helper I have created).

in sample above, unique count (of count org) would be 3, but, unique count with criteria would be 2.

I am using excel 2010.

I have tried many gyrations of sumproduct, frequency, match, and pairing with if, countif(s), etc.. and can get either unique count or match the criteria to work, but, not together.

I saw some formulas using rows, but, they had single cell criteria or I couldn't get them to work with a table that shrinks / grows with each update.

Any help would be greatly appreciated!

tsheets

 

[Aladin Akyurek]

Care concisely state what the criteria are and the result that you want to see?

 

[tsheets]

need unique count of 'count org' only if category is 1 or 4 (data is in a table - so, structured references).

Want to see: 2

 

[Aladin Akyurek]

need unique count of 'count org' only if category is 1 or 4 (data is in a table - so, structured references).

Want to see: 2

If we run unique count on 'org', we would have 3, not 2... That noted:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]site[/td][td]org[/td][td]count org[/td][td]category[/td][td][/td][td]cat1[/td][/tr]

[tr][td]

2​

[/td][td]site1[/td][td]org1[/td][td][/td][td]cat1[/td][td][/td][td]cat4[/td][/tr]

[tr][td]

3​

[/td][td]site2[/td][td]org2[/td][td]org2[/td][td]cat2[/td][td][/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]site3[/td][td]org1[/td][td][/td][td]cat3[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]site4[/td][td]org3[/td][td]org3[/td][td]cat4[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]site5[/td][td]org3[/td][td]org3[/td][td]cat4[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]site6[/td][td]org4[/td][td][/td][td]cat1[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]site7[/td][td]org1[/td][td][/td][td]cat1[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]site8[/td][td]org4[/td][td]org4[/td][td]cat1[/td][td][/td][td][/td][/tr]
[/table]

F3, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($C$2:$C$9<>"",
   IF(ISNUMBER(MATCH($D$2:$D$9,F$1:F$2,0)),
   MATCH($C$2:$C$9,$C$2:$C$9,0))),
   ROW($C$2:$C$9)-ROW($C$2)+1),1))

 

[tsheets]

A unique count of 'org' would be 4. A unique count of 'count org' would be 3. But, add the criteria of cat1 or cat4, and you end up with 2 unique 'count org'.

Since you are using cell references instead of structured references, are you proposing adding a helper column to the table that has only the criteria to match (the entire row would then include blanks, but, if the count is off by one, I can subtract that since it would be consistent).

I struggle with structured references....how do you do an expandable array with structured references?

edit: or maybe I can just use cell references with the criteria since there are thousands of rows and will always be fewer criteria to match? Not sure what would happen when I refresh the oledb table with statically entered data in a new column. I know formulas expand/shrink as needed. never tired static entry to see what would happen.

 

[Aladin Akyurek]

A unique count of 'org' would be 4. A unique count of 'count org' would be 3. But, add the criteria of cat1 or cat4, and you end up with 2 unique 'count org'.

A unique count run on 'org' with cat1 and cat4 as criteria is 3. But never mind...

Since you are using cell references instead of structured references, are you proposing adding a helper column to the table that has only the criteria to match (the entire row would then include blanks, but, if the count is off by one, I can subtract that since it would be consistent).

[...]

I'm not proposing any helper column at all. A criteria range is not a 'helper column'.

 

[tsheets]

Thank you for your help.

I just tested it using the table / structured references and it did work.

I apologize if my response was rude.

I will try this out tomorrow at work on the live data.

Thanks again!

tsheets

 

[tsheets]

I tested this today with the live data and everything worked out perfectly!

Here is the formula using structured references in case anyone runs across this in a future search:

=SUM(IF(FREQUENCY(IF(Table1[count org]<>"",IF(ISNUMBER(MATCH(Table1[category],E2:E3,0)),MATCH(Table1[count org],Table1[count org],0))),ROW(Table1[count org])-ROW(C2)+1),1))

Thanks much for your help, Aladin!

 

[Aladin Akyurek]

I tested this today with the live data and everything worked out perfectly!

Great.

Here is the formula using structured references in case anyone runs across this in a future search:

=SUM(IF(FREQUENCY(IF(Table1[count org]<>"",IF(ISNUMBER(MATCH(Table1[category],E2:E3,0)),MATCH(Table1[count org],Table1[count org],0))),ROW(Table1[count org])-ROW(C2)+1),1))

In fact, you can use ordinary definitions while keeping Table1.

category is defined as:

=Table1[category]

countorg is defined as:

=Table1[count org]

ivec is defined as:

=ROW(countorg)-ROW(INDEX(countorg,1,1))+1

The formula now becomes:

=SUM(IF(FREQUENCY(IF(countorg<>"",
   IF(ISNUMBER(MATCH(category,F$1:F$2,0)),
   MATCH(countorg,countorg,0))),ivec),1))

This formula is completely dynamic, due to Table facility. Ordinarily, one would need to define dynamic named ranges, but the Table facility replaces this action nicely.

See: https://dl.dropboxusercontent.com/u/65698317/tsheets unique count with criteria in a table.xlsx

Thanks much for your help, Aladin!

You are welcome.