count unique text values in a formula with multiple criteria

[manchoon]

Hi
I have data in columns like this below. A- sellers, B - shops, C- shop addresses

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"][TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD]AA[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]Ana[/TD]
[TD]AA[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]*[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD]BB[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]AA[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hank[/TD]
[TD]CC[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD]AA[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]CC[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hank[/TD]
[TD]FF[/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hank[/TD]
[TD]AA[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD]AA[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
.....

in cell H2 I need to count for example total number of shops ana has, or total number of addresses of AA shop...
I have data validation lists in cells E2, F2, G2 and I use them as criterias in formula. my formula should include something like a wildcard * which we use in sumifs because I might not indicate seller name and want to count total number of addresses of a specific shop, or I only choose seller name and I want number of addresses of every shop he/she has.

has anyone any idea how to do this? :/
thanks in advance

 

[Aladin Akyurek]

if E2=* F2=* G2=*, formula should count all shop addresses (without duplicates). In this case it's 7

a) when only E2 is available for example Tom, formula should count all of Tom's shop addresses, in this case it's 2 (Tom AA c; Tom CC f), when I choose Hank, the answer is 3 (Hank CC e; Hank FF g; Hank AA b).
b) When only F2 is available for example AA, formula should count all addresses of shop AA, it doesn't matter whether it belongs to Ana, Tom or Hank (answer = 3).
c) when I choose a or other letters here it doesn't make sense, because there is only one shop with one address, but I still have a list here (which I use in other formulas on the sheet), so if I choose a, b,c or anything else the answer must be 1.

It looks like G2 isn't necessary at all because:

E2 is either empty (or, if so desired, *) or a name like hank;

F2 is either empty (or, if so desired, *) or a shop like BB.

Try the following set up whether it yields the desired results under various combinations of E2 and F2. The claim is of course that it will...

[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][td]

G​

[/td][td]

H​

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

1​

[/td][td]seller[/td][td]shop[/td][td]shop address[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td] Ana[/td][td] AA[/td][td] a[/td][td] [/td][td] hank[/td][td] [/td][td] [/td][td]

3​

[/td][/tr]


[tr][td]

3​

[/td][td] Ana[/td][td] BB[/td][td] d[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

4​

[/td][td] Tom[/td][td] AA[/td][td] c[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

5​

[/td][td] Hank[/td][td] CC[/td][td] e[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

6​

[/td][td] Ana[/td][td] AA[/td][td] a[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

7​

[/td][td] Tom[/td][td] CC[/td][td] f[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

8​

[/td][td] Hank[/td][td] FF[/td][td] g[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

9​

[/td][td] Hank[/td][td] AA[/td][td] b[/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]


[tr][td]

10​

[/td][td] Ana[/td][td] AA[/td][td] a[/td][td] [/td][td] [/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$10=IF(OR(E2="",E2="*"),$A$2:$A$10,E2),
    IF($B$2:$B$10=IF(OR(F2="",F2="*"),$B$2:$B$10,F2),IF(1-($C$2:$C$10=""),
    MATCH($C$2:$C$10,$C$2:$C$10,0)))),ROW($C$2:$C$10)-ROW($C$2)+1),1))

Note that this FREQUENCY set up is faster than any conditional distinct/unique count formulation with COUNTIFS.