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.

 

[Aladin Akyurek]

ok, sorry to keep beating a dead horse but what if i dont want to define the range and just do A:A. P:P, etc...?

Excel 2007 or later would allow that, but that would mean testing a terrible amount of irrelevant cells, affecting the performance adversely.

 

[WhiteSS]

Excel 2007 or later would allow that, but that would mean testing a terrible amount of irrelevant cells, affecting the performance adversely.

thats what i thought so i just had the excel pull a more relevant table from the SQL data directly

 

[Aladin Akyurek]

thats what i thought so i just had the excel pull a more relevant table from the SQL data directly

Good idea.

 

[Sudarshan_1026]

Hi .. Can i ask one solution on the same topic

i have three columns of data as below

A.... B.... C....
MD1 X vs Y L
MD2 X vs Y L
MD2 X vs Y L

The output required is as below

I want to count unique values under column B with condition MD2 and L. For example in this case the output would be

A..... B.....
MD2 1

Any help is appreciated.

Sudi

 

[Aladin Akyurek]

Hi .. Can i ask one solution on the same topic

i have three columns of data as below

A.... B.... C....
MD1 X vs Y L
MD2 X vs Y L
MD2 X vs Y L

The output required is as below

I want to count unique values under column B with condition MD2 and L. For example in this case the output would be

A..... B.....
MD2 1

Any help is appreciated.

Sudi

It's unclear what you have in those cells, in column A, B, and C. Care to specify exactly?

 

[Sudarshan_1026]

Sorry but here you go with the information spread out.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Col A[/TD]
[TD="width: 64"]Col B[/TD]
[TD="width: 64"]Col C[/TD]
[/TR]
[TR]
[TD]MD1[/TD]
[TD]X vs Y[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]MD1[/TD]
[TD]X vs Y[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]MD1[/TD]
[TD]X vs Y[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]MD1[/TD]
[TD]X vs Y[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]MD2[/TD]
[TD]Y vs X[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]MD2[/TD]
[TD]Y vs X[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]MD2[/TD]
[TD]Y vs X[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]

For example, on entering MD1 in cell D2, it should give me an output of 1 in E2. which is the count of unique value of Col B with conditions MD1 ( which is my input) and a fixed second criteria "L".

 

[Aladin Akyurek]

Sorry but here you go with the information spread out.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Col A
[/TD]
[TD="width: 64"]Col B
[/TD]
[TD="width: 64"]Col C
[/TD]
[/TR]
[TR]
[TD]MD1
[/TD]
[TD]X vs Y
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]MD1
[/TD]
[TD]X vs Y
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]MD1
[/TD]
[TD]X vs Y
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]MD1
[/TD]
[TD]X vs Y
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]MD2
[/TD]
[TD]Y vs X
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]MD2
[/TD]
[TD]Y vs X
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]MD2
[/TD]
[TD]Y vs X
[/TD]
[TD]L
[/TD]
[/TR]
</tbody>[/TABLE]

For example, on entering MD1 in cell D2, it should give me an output of 1 in E2. which is the count of unique value of Col B with conditions MD1 ( which is my input) and a fixed second criteria "L".

F2: MD1

G2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$8<>"",IF($C$2:$C$8="L",
  IF($A$2:$A$8=$F2,MATCH("~"&$B$2:$B$8,$B$2:$B$8&"",0)))),
  ROW($B$2:$B$8)-ROW($B$2)+1),1))

 

[Sudarshan_1026]

Thanks Aladin, can i use the entire column as range or is it not advisable to do so. The data in source keeps adding in every day hence the question on using the entire column as range.

 

[Aladin Akyurek]

Thanks Aladin, can i use the entire column as range or is it not advisable to do so.

Not advisable

[quote[The data in source keeps adding in every day hence the question on using the entire column as range.[/QUOTE]

Either (1) convert the range in A:C into a table or (2) create dynamic named ranges. For (2), we need the same and the type of data you heve in those columns.

 

[Mehtaji]

Hi - I tried figuring my problem, very similar to this thread, on my own but have been unsuccessful. Hoping someone can help out.

I am trying to check for country in Column A and get count of values in column B after removing possible "#N/A" in column B. I also, need final count from column B that excludes any repeats. For data below, my final count from column B for each unique country in column A should be just 1 after removing #N/A and duplicates from column B

[TABLE="width: 182"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Asia[/TD]
[TD]0582.HK[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]ARWDA.AX[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]ARWDA.AX[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]ARWDA.AX[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]CFTM.PA[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]CFTM.PA[/TD]
[/TR]
</tbody>[/TABLE]