COUNTIF corresponding with other column

[awembridge]

Hi all,
I am trying to COUNT the values in a column, but only if they do NOT correspond to a duplicate value in a different column.

In other words, only count the Xs for only ONE instance of 101, 102 or 103 in the example below.
This is well beyond me and my powers, but could anyone kindly assist?

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 120px"><col width="120"><col width="120"><col width="120"></colgroup><tbody>[TR]
[TD](Desired)[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]COUNTA[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fred[/TD]
[TD]Paul[/TD]
[TD]Ian[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Can you explain which items are counted for each of your three results?

 

[Marcelo Branco]

It is not clear, at least for me, how to get the desired results: 6 for Paul, 7 for Fred and 6 for Ian.
Could you explain the logic to get such values?

M.

 

[Aladin Akyurek]

In B1 control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$4:$A$17),IF(B$4:B$17="X",$A$4:$A$17)),$A$4:$A$17),1))

 

[Marcelo Branco]

I think i understand what you are looking for.

See if this does what you need

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

(Desired)​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

COUNTA​

[/TD]
[TD]

9​

[/TD]
[TD]

8​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD]

Fred​

[/TD]
[TD]

Paul​

[/TD]
[TD]

Ian​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

101​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

102​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

103​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

104​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

105​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

106​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

101​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

107​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

101​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

108​

[/TD]
[TD][/TD]
[TD]

X​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

109​

[/TD]
[TD][/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

110​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[TD]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

102​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

103​

[/TD]
[TD][/TD]
[TD]

X​

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


Array formula in B1 copied across
=SUM(IF(FREQUENCY(IF(B$4:B$17="X",MATCH($A$4:$A$17,$A$4:$A$17,0)),ROW($A$4:$A$17)-ROW($A$4)+1),1))
Ctrl+Shift+Enter

M.

 

[awembridge]

Thanks for replying, let me try to explain more clearly.

Fred has 3 instances of 'X' corresponding to '101'. I want to count only the first of these.
Fred also has 2 instances of 'X' corresponding to '102'. I want to count only the first of these.
Fred also has instances of 'X' corresponding to '103' '104' '107' '110'.
Thus, my desired value for Fred is 6, (though there are 9 instances of 'X', I only want the first of each.)

Similarly, Paul has 2 instances of 'X' for '103'. I only want to count the first.

Ian only has instances of 'X' a maximum of once corresponding to 101-110. (Thus the COUNTA is equal to my desired output.)


My practical application is actually many more rows and columns, which is why I have tried to simplify it with this example.
Many thanks!

 

[Marcelo Branco]

If all the values in column A are numbers, not text, try the formula suggested by Aladin in post #4 . Otherwise, use the formula in post #5 .

M.

 

[awembridge]

Thanks to both Aladin and Marcelo - both wonderful solutions!