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]

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"]
<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]

With #N/A's in the relevant range...

[TABLE="width: 274"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]X
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Y
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]X
[/TD]
[TD="class: xl65, width: 110, bgcolor: transparent"]Unique Count
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Asia
[/TD]
[TD="class: xl65, bgcolor: transparent"]0582.HK
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Asia
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Australia
[/TD]
[TD="class: xl65, bgcolor: transparent"]ARWDA.AX
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Australia
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Australia
[/TD]
[TD="class: xl65, bgcolor: transparent"]ARWDA.AX
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Europe
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Australia
[/TD]
[TD="class: xl65, bgcolor: transparent"]ARWDA.AX
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Europe
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]#N/A
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Europe
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]#N/A
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Europe
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]#N/A
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Europe
[/TD]
[TD="class: xl65, bgcolor: transparent"]CFTM.PA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Europe
[/TD]
[TD="class: xl65, bgcolor: transparent"]CFTM.PA
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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

=SUM(IF(FREQUENCY(IF($A$2:$A$10=$D2,
  MATCH(IF(ISNA($B$2:$B$10),"#",$B$2:$B$10),
  IF(ISNA($B$2:$B$10),"#",$B$2:$B$10),0)),ROW($B$2:$B$10)-ROW($B$2)+1),1))-
  (SUM(IF($A$2:$A$10=$D2,IF(ISNA($B$2:$B$10),1)))>0)

By the way, are #N/A's in B2:B10 caused by a formula? If so, care to post that formula?

 

[vetteman]

Hi all, bumping an old thread.

Here is my table that i am struggling with:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project
[/TD]
[TD]Forecasted[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]N[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]N[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]N[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I have figured out how to count the unique projects in col A (5 in this case), i am now trying to count the unique projects that were forecasted (should be 3). I am struggling with the If(frequency when there is another embedded If statement.

thanks in advance!

 

[Aladin Akyurek]

Hi all, bumping an old thread.

Here is my table that i am struggling with:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Project

[/TD]
[TD]Forecasted
[/TD]
[TD]Item
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]N
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]N
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]N
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]Y
[/TD]
[TD]1
[/TD]
[/TR]
</TBODY>[/TABLE]


I have figured out how to count the unique projects in col A (5 in this case), i am now trying to count the unique projects that were forecasted (should be 3). I am struggling with the If(frequency when there is another embedded If statement.

thanks in advance!

Control+shift+enter, not just enter:

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

 

[vetteman]

Thank you so much! I never would have gotten there

 

[Aladin Akyurek]

Thank you so much! I never would have gotten there

You are welcome. Thanks for providing feedback.

 

[Sean Christian]

Hello All! This is my first post to Mr. Excel, so I'm kind of excited... but confused. I've tried implementing this Sum, If, Frequency formula to my spreadsheet, but have had no luck.

[TABLE="width: 200"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]EmployeeID[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD]172705497[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]172705497[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]172717693[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]172717693[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]172724808[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]172737704[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]172744009[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]

I need to figure out how many unique Employee ID's are in Department 1000. I may just be having a two day brain fog, but I can't figure out how to do this... Any help is greatly appreciated!

 

[Aladin Akyurek]

Hello All! This is my first post to Mr. Excel, so I'm kind of excited... but confused. I've tried implementing this Sum, If, Frequency formula to my spreadsheet, but have had no luck.

[TABLE="width: 200"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]EmployeeID
[/TD]
[TD]Dept
[/TD]
[/TR]
[TR]
[TD]172705497
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]172705497
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]172717693
[/TD]
[TD]4000
[/TD]
[/TR]
[TR]
[TD]172717693
[/TD]
[TD]4000
[/TD]
[/TR]
[TR]
[TD]172724808
[/TD]
[TD]1100
[/TD]
[/TR]
[TR]
[TD]172737704
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]172744009
[/TD]
[TD]1000
[/TD]
[/TR]
</TBODY>[/TABLE]

I need to figure out how many unique Employee ID's are in Department 1000. I may just be having a two day brain fog, but I can't figure out how to do this... Any help is greatly appreciated!

E2: 1000 (a department of interest)

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

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

 

[Sean Christian]

I'm getting an error stating that Excel ran out of resources? Is that normal...?

 

[Aladin Akyurek]

I'm getting an error stating that Excel ran out of resources? Is that normal...?

No, it's not normal for such amount of data...

See:

https://dl.dropboxusercontent.com/u/65698317/aaConditionalUniqueCount%20Sean%20Christian.xlsx

 

[Sean Christian]

Hi Aladin!

Thank you so much for all of your help. I was converting your formula range to include the entire columns, instead A2:A8. Is that possible, or is that the reason I'm getting the error?