Count unique text values based on condition in another column

H

himihai

New Member
Joined
Oct 8, 2009
Messages
5
Hello,

I need to count uniques text values in a column that contains names.
But I only need to count the unique values that are satisfying a condition in another column.

Example:

<TABLE style="WIDTH: 166pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=222 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: black" width=103 height=20>Group</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=119>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>c</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 4</TD></TR></TBODY></TABLE>

So I need to count unique names from group "a"

I already got the formula for counting the unique values from the whole list, and I just need to add the condition that would restrict the search only to one group (condition).

Any ideas?
 
Sort by date Sort by votes
When I typed in the formula I get "#NAME?" in the cell. I did not use the workbook defined names in my equation. Am I suppose to?
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Aladin Akyurek said:
@CycleGirl

Is the expected result for Nancy's Sewing Shop 2?
Click to expand...

CycleGirl said:
Yes
Click to expand...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Vendor Item Id[/td][td]Manufacturer Name[/td][td][/td][td]Manufacturer Name[/td][td]Unique Ciunt[/td][/tr]

[tr][td]
2​
[/td][td]
335​
[/td][td]Nestle[/td][td][/td][td]Nestle[/td][td]
3​
[/td][/tr]

[tr][td]
3​
[/td][td]
41​
[/td][td]Monsanto[/td][td][/td][td]Monsanto[/td][td]
2​
[/td][/tr]

[tr][td]
4​
[/td][td]B75453[/td][td]Texas Instruments[/td][td][/td][td]Texas Instruments[/td][td]
2​
[/td][/tr]

[tr][td]
5​
[/td][td]
2345​
[/td][td]Shell Inc[/td][td][/td][td]Shell Inc[/td][td]
1​
[/td][/tr]

[tr][td]
6​
[/td][td]
87643​
[/td][td]Nancy's Sewing Shop[/td][td][/td][td]Nancy's Sewing Shop[/td][td]
2​
[/td][/tr]

[tr][td]
7​
[/td][td]
689043​
[/td][td]Tree Farm Nursery[/td][td][/td][td]Tree Farm Nursery[/td][td]
3​
[/td][/tr]

[tr][td]
8​
[/td][td]M5678[/td][td]Nestle[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
41​
[/td][td]Monsanto[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
87643​
[/td][td]Nancy's Sewing Shop[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
689043​
[/td][td]Tree Farm Nursery[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]N983762[/td][td]Nancy's Sewing Shop[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]v987473[/td][td]Tree Farm Nursery[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td]
34982​
[/td][td]Nestle[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td]
598734​
[/td][td]Monsanto[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td]
9087543​
[/td][td]Texas Instruments[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td]N983762[/td][td]Nancy's Sewing Shop[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td]v765498[/td][td]Tree Farm Nursery[/td][td][/td][td][/td][td][/td][/tr]
[/table]

E2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=SUM(IF(FREQUENCY(IF($A$2:$A$18<>"",
    IF($B$2:$B$18=D2,MATCH("~"&$A$2:$A$18,$A$2:$A$18&"",0))),
    ROW($A$2:$A$18)-ROW($A$2)+1),1))

See the workbook which implements this conditional unique count formula:
https://dl.dropboxusercontent.com/u...ues based on condition in another column.xlsx
 
Upvote 0
Aladin Akyurek said:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Vendor Item Id[/TD]
[TD]Manufacturer Name[/TD]
[TD][/TD]
[TD]Manufacturer Name[/TD]
[TD]Unique Ciunt[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
335​
[/TD]
[TD]Nestle[/TD]
[TD][/TD]
[TD]Nestle[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
41​
[/TD]
[TD]Monsanto[/TD]
[TD][/TD]
[TD]Monsanto[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]B75453[/TD]
[TD]Texas Instruments[/TD]
[TD][/TD]
[TD]Texas Instruments[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
2345​
[/TD]
[TD]Shell Inc[/TD]
[TD][/TD]
[TD]Shell Inc[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
87643​
[/TD]
[TD]Nancy's Sewing Shop[/TD]
[TD][/TD]
[TD]Nancy's Sewing Shop[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
689043​
[/TD]
[TD]Tree Farm Nursery[/TD]
[TD][/TD]
[TD]Tree Farm Nursery[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]M5678[/TD]
[TD]Nestle[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
41​
[/TD]
[TD]Monsanto[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
87643​
[/TD]
[TD]Nancy's Sewing Shop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
689043​
[/TD]
[TD]Tree Farm Nursery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]N983762[/TD]
[TD]Nancy's Sewing Shop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]v987473[/TD]
[TD]Tree Farm Nursery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
34982​
[/TD]
[TD]Nestle[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]
598734​
[/TD]
[TD]Monsanto[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]
9087543​
[/TD]
[TD]Texas Instruments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]N983762[/TD]
[TD]Nancy's Sewing Shop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]v765498[/TD]
[TD]Tree Farm Nursery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


E2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=SUM(IF(FREQUENCY(IF($A$2:$A$18<>"",
    IF($B$2:$B$18=D2,MATCH("~"&$A$2:$A$18,$A$2:$A$18&"",0))),
    ROW($A$2:$A$18)-ROW($A$2)+1),1))

See the workbook which implements this conditional unique count formula:
https://dl.dropboxusercontent.com/u...ues based on condition in another column.xlsx
Click to expand...

This has given me a return of #N/A. I cannot get to the dropbox contents with my current connection. I will need to try to get it with different connection in a couple of hours. I will let you know if I can get to it. Thank you for your help.
 
Upvote 0
Aladin Akyurek said:
@CycleGirl

Is the expected result for Nancy's Sewing Shop 2?
Click to expand...

CycleGirl said:
This has given me a return of #N/A. I cannot get to the dropbox contents with my current connection. I will need to try to get it with different connection in a couple of hours. I will let you know if I can get to it. Thank you for your help.
Click to expand...

Perhaps one point... Control+shift+enter means: Press down the control and the shift keys at the same time, while you hit the enter key.
 
Upvote 0
@Aladin!
Your formula is very similar to my formula (I learned from you) post #19, but I don't understand this sign "~" and this &"", see belowe the formula and also in my Italian KeyBoard I don't have thi charater "~" however you call it
can you help me!


=SUM(IF(FREQUENCY(IF($A$2:$A$18<>"",
IF($B$2:$B$18=D2,MATCH("~"&$A$2:$A$18,$A$2:$A$18&"",0))),
ROW($A$2:$A$18)-ROW($A$2)+1),1))
 
Upvote 0
himihai said:
Hello,

I need to count uniques text values in a column that contains names.
But I only need to count the unique values that are satisfying a condition in another column.

Example:

<TABLE style="WIDTH: 166pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=222 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: black" width=103 height=20>Group</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=119>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>c</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Name 4</TD></TR></TBODY></TABLE>

So I need to count unique names from group "a"

I already got the formula for counting the unique values from the whole list, and I just need to add the condition that would restrict the search only to one group (condition).

Any ideas?
Click to expand...

I have a similar problem, but a little different. I have to count the number of times a value appears in a column, if the value in another column is unique. Any suggestions?
 
Upvote 0
I have a similar problem, but a little different. I have to count the number of times a value appears in a column, but only once for every time that the value in another column appears. E.g. I have order numbers in one column, but they are used every time an item is ordered against that number, therefore a single project could have the same order number appear multiple times. In another column I have the type of work. I need to count the number of times a type of work appears in the data, but do not want to double-count it if the same order number is used again.
 
Upvote 0
Natashavo said:
I have a similar problem, but a little different. I have to count the number of times a value appears in a column, if the value in another column is unique. Any suggestions?
Click to expand...

Natashavo said:
I have a similar problem, but a little different. I have to count the number of times a value appears in a column, but only once for every time that the value in another column appears. E.g. I have order numbers in one column, but they are used every time an item is ordered against that number, therefore a single project could have the same order number appear multiple times. In another column I have the type of work. I need to count the number of times a type of work appears in the data, but do not want to double-count it if the same order number is used again.
Click to expand...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr][tr][td]
2​
[/td][td]
2​
[/td][td]x[/td][td][/td][td]x[/td][/tr]
[tr][td]
3​
[/td][td]
2​
[/td][td]y[/td][td][/td][td]
2​
[/td][/tr]
[tr][td]
4​
[/td][td]
3​
[/td][td]x[/td][td][/td][td][/td][/tr]
[tr][td]
5​
[/td][td]
2​
[/td][td]x[/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td]
3​
[/td][td]w[/td][td][/td][td][/td][/tr]
[/table]

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

=SUM(IF(FREQUENCY(IF(1-(A2:A6=""),IF(1-(B2:B6=""),IF(B2:B6="x",MATCH(A2:A6&"|"&B2:B6,A2:A6&"|"&B2:B6,0)))),ROW(A2:A6)-ROW(A2)+1),1))

is this what you are after?
 
Upvote 0
I also have a similar issue but am running into an issue with the way array 'sorts' the data.

I'm using the formula "=SUM(IF(FREQUENCY(IF(B42:B140=B42,IF(B42:B140<>"",MATCH("~"&A42:A140,A42:A140&"",0))),ROW(A42:A140)-ROW(A42)+1),1))" and it is working well but I notice when it hits a row with multiple values for my given criteria, it only counts down and thus only the first iteration is correct.

For Example:

[TABLE="width: 313"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780860[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780860[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780860[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]HME[/TD]
[TD]780860[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]HME[/TD]
[TD]780860[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]HME[/TD]
[TD]780860[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780866[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TBMRES[/TD]
[TD]780867[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TBLPCRRES[/TD]
[TD]780867[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TBLPCRRES[/TD]
[TD]780867[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TBL_CAP_MGA[/TD]
[TD]780867[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TBHRES[/TD]
[TD]780867[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


I would expect it to return the result of '5' for all rows with '780860' in column B but it appears to only look 'down' due to the array. Is there an alternative way of calculating this without the array issue?
 
Upvote 0
You need to lock the relevant ranges in the formula, i.e.

In C42 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$42:$B$140=$B42,IF($B$42:$B$140<>"",MATCH("~"&$A$42:$A$140,$A$42:$A$140&"",0))),ROW($A$42:$A$140)-ROW($A$42)+1),1))

huskerjhansen said:
I also have a similar issue but am running into an issue with the way array 'sorts' the data.

I'm using the formula "=SUM(IF(FREQUENCY(IF(B42:B140=B42,IF(B42:B140<>"",MATCH("~"&A42:A140,A42:A140&"",0))),ROW(A42:A140)-ROW(A42)+1),1))" and it is working well but I notice when it hits a row with multiple values for my given criteria, it only counts down and thus only the first iteration is correct.

For Example:

[TABLE="width: 313"]
<tbody>[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BAB[/TD]
[TD]780860[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]780860[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780860[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780860[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780860[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]HME[/TD]
[TD]780860[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]HME[/TD]
[TD]780860[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]HME[/TD]
[TD]780860[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BMMG[/TD]
[TD]780860[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]LAA[/TD]
[TD]780866[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TBMRES[/TD]
[TD]780867[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TBLPCRRES[/TD]
[TD]780867[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TBLPCRRES[/TD]
[TD]780867[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TBL_CAP_MGA[/TD]
[TD]780867[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TBHRES[/TD]
[TD]780867[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


I would expect it to return the result of '5' for all rows with '780860' in column B but it appears to only look 'down' due to the array. Is there an alternative way of calculating this without the array issue?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
538
mumps
mumps
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
92
Fluff
Fluff

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top