Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[tbecker3]

I think your asking if Column B has more than just MACCL1 & MACCK, that is Yes. There are 12 types in column B, I only want those unique to MACCL1 And MACCK. The column for my current data set is 24307 rows.

 

[Aladin Akyurek]

I think your asking if Column B has more than just MACCL1 & MACCK, that is Yes. There are 12 types in column B, I only want those unique to MACCL1 And MACCK. The column for my current data set is 24307 rows.

I was asking whether you would impose more than 2 criteria at the same time, say MACCL1, MACKK, MACCT for example?

 

[tbecker3]

Potentially that may be required, or a variable from another column.

 

[tbecker3]

And, what is wrong with this formula? I want Unique count from Q, of MACCK in AP, But only if CD = Y. As is I get #N/A. What am I missing, where?

=SUM(IF(FREQUENCY(IF(1-($Q$6:$Q$24301=""),IF($CD6:$CD$24301="Y",IF($AP$6:$AP$24301="MACCK",MATCH($Q$6:$Q$24301,$Q$6:$Q$24301,0))),ROW($Q$6:$Q$24301)-ROW($Q$6)+1),1),0))

 

[Aladin Akyurek]

And, what is wrong with this formula? I want Unique count from Q, of MACCK in AP, But only if CD = Y. As is I get #N/A. What am I missing, where?

=SUM(IF(FREQUENCY(IF(1-($Q$6:$Q$24301=""),IF($CD6:$CD$24301="Y",IF($AP$6:$AP$24301="MACCK",MATCH($Q$6:$Q$24301,$Q$6:$Q$24301,0))),ROW($Q$6:$Q$24301)-ROW($Q$6)+1),1),0))

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($Q$6:$Q$24301=""),IF($CD6:$CD$24301="Y",IF($AP$6:$AP$24301="MACCK",MATCH($Q$6:$Q$24301,$Q$6:$Q$24301,0)))),ROW($Q$6:$Q$24301)-ROW($Q$6)+1),1),0))

 

[Aladin Akyurek]

Equal both, not equal either or... Do you ever have more than two such criteria?

I think your asking if Column B has more than just MACCL1 & MACCK, that is Yes. There are 12 types in column B, I only want those unique to MACCL1 And MACCK. The column for my current data set is 24307 rows.

I was asking whether you would impose more than 2 criteria at the same time, say MACCL1, MACKK, MACCT for example?

Potentially that may be required, or a variable from another column.

In case of two...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

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

1​

[/td][td]GPI[/td][td]X[/td][td][/td][td]MACCL1[/td][/tr]


[tr][td]

2​

[/td][td]01100040100315[/td][td]MACCL1[/td][td][/td][td]MACCK[/td][/tr]


[tr][td]

3​

[/td][td]01100040100315[/td][td]MACCK[/td][td][/td][td]

2​

[/td][/tr]


[tr][td]

4​

[/td][td]01200010100105[/td][td]MACCL1[/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]01200010100110[/td][td]MACCL1[/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]01200010100110[/td][td]MACCK[/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]01200010100303[/td][td]MACCK[/td][td][/td][td][/td][/tr]
[/table]

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

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(IF($B$2:$B$7=D2,$A$2:$A$7,"#"),
    IF($B$2:$B$7=D1,$A$2:$A$7),0)),MATCH($A$2:$A$7,$A$2:$A$7,0)),
    ROW($A$2:$A$7)-ROW($A$2)+1),1))

 

[tbecker3]

Awesome! Got it, and the follow up I sent where the variable is Y.
Thanks so much for your expertize!!!!!

 

[Aladin Akyurek]

Awesome! Got it, and the follow up I sent where the variable is Y.
Thanks so much for your expertize!!!!!

You are welcome. Sorry I don't get the "variable is Y" bit.

 

[tbecker3]

Variable Y from post #275 above, the extra parin did not work, but this did. With L1 being Y.
=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(IF($AP$6:$AP$24301=K2,$Q$6:$Q$24301,"#"),IF($CD$6:$CD$24301=L1,$Q$6:$Q$24301),0)),MATCH($Q$6:$Q$24301,$Q$6:$Q$24301,0)),ROW($Q$6:$Q$24301)-ROW($Q$6)+1),1))

Thanks Again!

 

[Aladin Akyurek]

Variable Y from post #275 above, the extra parin did not work, but this did. With L1 being Y.
=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(IF($AP$6:$AP$24301=K2,$Q$6:$Q$24301,"#"),IF($CD$6:$CD$24301=L1,$Q$6:$Q$24301),0)),MATCH($Q$6:$Q$24301,$Q$6:$Q$24301,0)),ROW($Q$6:$Q$24301)-ROW($Q$6)+1),1))

Thanks Again!

No wonder. I did not take the question of post #275 as the same question I have answered in post #276 at all!...