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

 

[eb101]

It looks like you are best off with COUNTIFS formula. Have you tried that and it did not work?

 

[manchoon]

Yes I tried but it doesn't exclude duplicate values

 

[eb101]

Yes I tried but it doesn't exclude duplicate values

When you say H2, do you define the field as Ana and than countif using that parameter against the data you have? As far as duplicate's, did you try data tab -> remove duplicates function? That should remove duplicates from data if they are reoccurring more than they should

 

[manchoon]

In H2 I want to write a formula, which will count data by the criterias I will choose in cells E2, F2, G2. For example I will choose Ana (in E2) and AA (in F2), the result must be 1, I might choose only AA and result must be 3...
My data range is variable, so I need a formula which will change automatically when I refresh the data. I can't erase the duplicate values in data range completely, because I use this range in other formulas too and I need the duplicates there

 

[Aladin Akyurek]

Is always one value present in E2:F2?

If so...

(a) When only E2 is available, what must be counted in H2?

(b) When only F2 is available, what must be counted in H2?

(c) when only G2 (apparently always *) is available, what must be counted in H2?

 

[hetfieldkaloo]

Use an array formula if you are not comfortable using vba with dictionary count-
this formula should work in cell H2:
=SUM(IF($A$2:$A$11="Ana",1/(COUNTIFS($A$2:$A$11,"Ana",$B$2:$B$11,$B$2:$B$11)),0))

 

[manchoon]

Is always one value present in E2:F2?

If so...

(a) When only E2 is available, what must be counted in H2?

(b) When only F2 is available, what must be counted in H2?

(c) when only G2 (apparently always *) is available, what must be counted in H2?

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.

 

[manchoon]

Use an array formula if you are not comfortable using vba with dictionary count-
this formula should work in cell H2:
=SUM(IF($A$2:$A$11="Ana",1/(COUNTIFS($A$2:$A$11,"Ana",$B$2:$B$11,$B$2:$B$11)),0))

Thank you, it works perfectly with one criteria. But I don't understand how can I add another 2 criterias? Could you please help me out?
Does this formula recognize wildcard *? If I want to count all addresses of Hank for example, without specifying the shop?

 

[hetfieldkaloo]

Machoon - this array formula is simple, to add multiple criteria you would need to append the first IF criteria section and also add the same one the 1/Countif Section:
=SUM(IF(($A$2:$A$11="Ana")*(Range2="Criteria 2")*(Range3="Criteria 3"),1/(COUNTIFS($A$2:$A$11,"Ana",Range2, Criteria2, Range3,Criteria3,$B$2:$B$11,$B$2:$B$11)),0))

Let me know if the explanation helps and if you were able to get your desired result