# Count unique values in a table that meet one or two criterias

1. ## Count unique values in a table that meet one or two criterias

Hi, i need some help with an array formel.
I have to count unique values in a table that meet one or two criterias.
I`ve added an excel file that explais it

simplified table:
a|b|c
Id number |Week | Average deviation
5 |40| |5 %
5 |40 |0 %
80 |40 |30 %
10 |39 |15 %
10 |40 |0 %
5 |42 |20 %
5 |43 |30 %
80 |40 |28 %
80 |40 |0 %
10 |42 |0 %
10 |43 |0 %
10 |42 |15 %
80 |40 |0 %
5 |41 |0 %

My rapport:
G|H|I
Id number | Total number of uniue weeks | total number of unique weeks with an deviation that is higher than 0%
5 |"=sumuniquevalues in B:B that has their A value =5 | "=sumuniquevalues in B:B that has their A value =5 & their C value is >0
10 |"=sumuniquevalues in B:B that has their A value =10 | "=sumuniquevalues in B:B that has their A value =10 & their C value is >0
80 |"=sumuniquevalues in B:B that has their A value =80 | "=sumuniquevalues in B:B that has their A value =80 & their C value is >0

Best regards
Grenny

2. ## Re: Count unique values in a table that meet one or two criterias

In H5:
=SUM(INDEX((\$B\$3:\$B\$16=G5)/COUNTIFS(\$B\$3:\$B\$16,\$B\$3:\$B\$16&"",\$C\$3:\$C\$16,\$C\$3:\$C\$16&""),0))

and in I5:
=SUM(INDEX((\$B\$3:\$B\$16=G5)*(\$D\$3:\$D\$16>0)/COUNTIFS(\$B\$3:\$B\$16,\$B\$3:\$B\$16&"",\$C\$3:\$C\$16,\$C\$3:\$C\$16&"",\$D\$3:\$D\$16,\$D\$3:\$D\$16&""),0))

You may need ; as the separator.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1