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:

site	org	count org	category
site1	org1		cat1
site2	org2	org2	cat2
site3	org1		cat3
site4	org3	org3	cat4
site5	org3	org3	cat4
site6	org4		cat1
site7	org1		cat1
site8	org4	org4	cat1

<tbody>
</tbody>

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:

Row\Col	A​	B​	C​	D​	E​	F​
1​	site	org	count org	category		cat1
2​	site1	org1		cat1		cat4
3​	site2	org2	org2	cat2		2​
4​	site3	org1		cat3
5​	site4	org3	org3	cat4
6​	site5	org3	org3	cat4
7​	site6	org4		cat1
8​	site7	org1		cat1
9​	site8	org4	org4	cat1

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.