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 found how to copy and control shift enter on another site. Tried it again, it did succeed, but and after I update with REAL column and cell references, I still get a Value error. Darn It.

 

[Aladin Akyurek]

I found how to copy and control shift enter on another site. Tried it again, it did succeed,

Hmmm...

but and after I update with REAL column and cell references, I still get a Value error. Darn It.

You need to re-apply control+shift+enter after each edit to the formula.

 

[tbecker3]

Yeah, I finally understand. Thank You so much. It works!
Can you provide a step explanation so I can explain it to my manager?

 

[Aladin Akyurek]

Yeah, I finally understand. Thank You so much. It works!

You are welcome. Thanks for the update.

Can you provide a step explanation so I can explain it to my manager?

Here is a link with an explanation of SUM(IF(FREQUENCY(...),1))...

http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949
[ Note that A2:A11<>"" is the same thing as 1-(A2:A11=""). ]

 

[tbecker3]

Thanks Aladin, Did you get my reply?
I also need to have a unique count of column A where it equals both MACCL1 and MACCK in column B.
Is this possible?

 

[Aladin Akyurek]

Thanks Aladin, Did you get my reply?
I also need to have a unique count of column A where it equals both MACCL1 and MACCK in column B.
Is this possible?

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$400=""),IF(ISNUMBER(MATCH($B$2:$B$400,{"MACCL1","MACCK"},0)),MATCH($A$2:$A$400,$A$2:$A$400,0))),ROW($A$2:$A$400)-ROW($A$2)+1),1))

 

[tbecker3]

Hello, Thanks again. But, this counts ALL values in column A that equal MACCL1 or MACCK. I need to count Only the unique count of values that equal both MACCL1 and MACCK. Make sense?

 

[Aladin Akyurek]

Hello, Thanks again. But, this counts ALL values in column A that equal MACCL1 or MACCK. I need to count Only the unique count of values that equal both MACCL1 and MACCK. Make sense?

How do you mean? It does a unique count... Probably not what you want or suppose. If so, try to post a scaled-down sample along with the desired result.

 

[tbecker3]

With this subset below, the result should be 2. Of the 4 unique GPIs - Column A, only 2 of them are equal to both MACCL1 and MACCK in Column B. Make sense?

GPI-Column A Column B
01100040100315 MACCL1
01100040100315 MACCK
01200010100105 MACCL1
01200010100110 MACCL1
01200010100110 MACCK
01200010100303 MACCK

 

[Aladin Akyurek]

With this subset below, the result should be 2. Of the 4 unique GPIs - Column A, only 2 of them are equal to both MACCL1 and MACCK in Column B. Make sense?

GPI-Column A Column B
01100040100315 MACCL1
01100040100315 MACCK
01200010100105 MACCL1
01200010100110 MACCL1
01200010100110 MACCK
01200010100303 MACCK

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