Count values based on criteria with no duplicates

[leterrier]

Apologies if this has been answered elsewhere but I cannot find it.

I need an excel formula to count text values (that may include blanks) in one column based on criteria from an adjacent column. I only want to count duplicates once and exclude blanks. Eg if cells in column contain "x", count all corresponding unique values in column B excluding blanks. It's sounds simple but it has foxed me so for. I'm grateful for your learned help! PS bonus if it can be achieved without using an array formula but I can live with it if it's the best solution. Thank you.

 

[venkat1926]

do you want to find no. of cells in column B containing "X" as one word or
do you want to find no. of cells in columb containing various words containing character "x"

if former you use count if

suppose col B contains

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]x[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]v[/TD]
[/TR]
[TR]
[TD]x[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD]f[/TD]
[/TR]
[TR]
[TD]g

B2 blank

then use formula
=COUNTIF(B1:B10,"x")

[/TD]
[/TR]
</tbody>[/TABLE]

 

[leterrier]

Thank you Venkat1926, I didn't make myself too clear in this thread, I will have a think on how to rephrase and try again. Thank you once again for your time!

 

[Aladin Akyurek]

Apologies if this has been answered elsewhere but I cannot find it.

I need an excel formula to count text values (that may include blanks) in one column based on criteria from an adjacent column. I only want to count duplicates once and exclude blanks. Eg if cells in column contain "x", count all corresponding unique values in column B excluding blanks. It's sounds simple but it has foxed me so for. I'm grateful for your learned help! PS bonus if it can be achieved without using an array formula but I can live with it if it's the best solution. Thank you.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$100<>"",IF($C$2:$C$100="x",
  MATCH("~"&$B$2:$B$100,$B$2:$B$100&"",0))),
  ROW($B$2:$B$100)-ROW($B$2)+1),1))

 

[leterrier]

Aladin, Thank you for responding, I will try your suggestion and let you know!!!!

 

[leterrier]

Aladin, Thank you so much for taking the time to respond, unfortunately, this formula is returning a "you have entered too few arguments for this function" error message

 

[leterrier]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Garment Type (A)
[/TD]
[TD]Colour (B)
[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Hats
[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cots[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scarves
[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hats
[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Coats
[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scarves[/TD]
[TD]blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hats
[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Coats[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hats
[/TD]
[TD][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD]Scarves
[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




















I wish to know how many different coloured hats have been sold, the answer is 2 (red and green).

The formula I need is: if the criteria in column A is "hats", how many unique values are in column B - excluding blanks? The answer is 2 (red and green)

Thanks

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$11=""),IF($A$2:$A$11="Hats",
  MATCH("~"&$B$2:$B$11,$B$2:$B$11&"",0))),ROW($B$2:$B$11)-ROW($B$2)+1),1))

 

[leterrier]

Aladin, Thank you again for your help, its working perfectly, I was not getting the array right. Thanks for your patience!!!!

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$100<>"",IF($C$2:$C$100="x",
  MATCH("~"&$B$2:$B$100,$B$2:$B$100&"",0))),
  ROW($B$2:$B$100)-ROW($B$2)+1),1))

 

[Aladin Akyurek]

Aladin, Thank you again for your help, its working perfectly, I was not getting the array right. Thanks for your patience!!!!

You are welcome.