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 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?

EmployeeID and Dept are true numbers, right?

 

[Sean Christian]

EmployeeID and Dept are true numbers, right?

I believe so. It is an actual number, but the format is "general."

 

[Aladin Akyurek]

EmployeeID and Dept are true numbers, right?

I believe so. It is an actual number, but the format is "general."

[TABLE="width: 456"]
<TBODY>[TR]
[TD="class: xl67, width: 150, bgcolor: white"]EmployeeID
[/TD]
[TD="class: xl67, width: 90, bgcolor: white"]Dept
[/TD]
[TD="class: xl68, width: 26, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 25, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 118, bgcolor: white"]Dept List
[/TD]
[TD="class: xl67, width: 201, bgcolor: white"]Count Distinct ID's
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172705497
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]1000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"]1000
[/TD]
[TD="class: xl65, width: 201, bgcolor: white"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172705497
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]1000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"]4000
[/TD]
[TD="class: xl65, width: 201, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172717693
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]4000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"]1100
[/TD]
[TD="class: xl65, width: 201, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172717693
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]4000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 201, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172724808
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]1100
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 201, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172737704
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]1000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 201, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 150, bgcolor: white"]172744009
[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]1000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 201, bgcolor: white"][/TD]
[/TR]
</TBODY>[/TABLE]

Let Sheet1, A:B, from row 2 downwards, house the data of interest.

Define EmpID as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

Dept as:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

Ivec as:

=ROW(EmpID)-ROW(INDEX(EmpID,1,1))+1

E2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Dept,SMALL(IF(FREQUENCY(IF(Dept<>"",
  MATCH(Dept,Dept,0)),Ivec),Ivec),ROWS($E$2:E2))),"")

F2, control+shift+enter and copy down:

=IF($E2="","",SUM(IF(FREQUENCY(IF(EmpID<>"",IF(Dept=$E2,
  MATCH(EmpID,EmpID,0))),ROW(EmpID)-ROW(INDEX(EmpID,1,1))+1),1)))

See:
https://dl.dropboxusercontent.com/u...lUniqueCount DynamicSetUp Sean Christian.xlsx

 

[Sean Christian]

THIS WORKED PERFECTLY!!! Thank you so much Aladin! You are AMAZING! :D

 

[Aladin Akyurek]

THIS WORKED PERFECTLY!!! Thank you so much Aladin! You are AMAZING! :D

Great. Thanks for providing feedback.

 

[jbene]

Hello!

I've read through this page and tried many of the formulas but I can't figure out how to make it work. I have a (judging from the others) rather simple thing I want to do.

I would like to count how many times a unique name appears in different situations:

1) Each group
2) Each month

1) For example in the table below I would want a formula to tell me that it's 2 persons in the Customer group and 1 person in the Sales group (two separate formulas doesn't matter).
2) And one formula to tell me that there was 3 persons in total in the month/year of 01-2014.



If anyone can help me with formulas to do this it would be totally fantastic!

(The row with dates is not here for any reason in this example, but I have it in my excel sheet for pivot table reasons...)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hello!

I've read through this page and tried many of the formulas but I can't figure out how to make it work. I have a (judging from the others) rather simple thing I want to do.

I would like to count how many times a unique name appears in different situations:

1) Each group
2) Each month

1) For example in the table below I would want a formula to tell me that it's 2 persons in the Customer group and 1 person in the Sales group (two separate formulas doesn't matter).
2) And one formula to tell me that there was 3 persons in total in the month/year of 01-2014.



If anyone can help me with formulas to do this it would be totally fantastic!

(The row with dates is not here for any reason in this example, but I have it in my excel sheet for pivot table reasons...)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]Jessica Summers[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Customer[/TD]
[TD]2014-01-01[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Sales[/TD]
[TD]2014-01-15[/TD]
[TD]01-2014[/TD]
[/TR]
</tbody>[/TABLE]

E1: Customer, F1: Sales

E2, control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF($A$1:$A$14<>"",
  IF($B$1:$B$14=E$1,MATCH($A$1:$A$14,$A$1:$A$14,0))),
  ROW($A$1:$A$14)-ROW($A$1)+1),1))

G1: 01-01-2014

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

=SUM(IF(FREQUENCY(IF($A$1:$A$14<>"",
  IF($C$1:$C$14-DAY($C$1:$C$14)+1=G$1,
  MATCH($A$1:$A$14,$A$1:$A$14,0))),
  ROW($A$1:$A$14)-ROW$A$1)+1),1))

 

[Beafernand]

E1: Customer, F1: Sales

E2, control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF($A$1:$A$14<>"",
  IF($B$1:$B$14=E$1,MATCH($A$1:$A$14,$A$1:$A$14,0))),
  ROW($A$1:$A$14)-ROW($A$1)+1),1))

Hello, I have read this entire thread, and have a very similar situation as the others, but I can't seem to get my formula to work. I have two parts, and am stuck with part A still. Please I'd appreciate any help provided. Part A: identify number of unique deployments by Customer, and then Part B: to identify the number of unique contacts within each deployment for that Customer. Below is my formula, and I am having trouble copying it down and showing the correct results.[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]A Customer[/TD]
[TD]B Deployment[/TD]
[TD]C Contacts[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya1[/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya1[/TD]
[TD]Susie[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya2[/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya3[/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerB[/TD]
[TD]deployb1[/TD]
[TD]Frank[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerB[/TD]
[TD]deployb1[/TD]
[TD]Frank[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerB[/TD]
[TD]deployb2[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerB[/TD]
[TD]deployb1[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerB[/TD]
[TD]deployb3[/TD]
[TD]Susie[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerB[/TD]
[TD]deployb3[/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerC[/TD]
[TD]deployc3[/TD]
[TD]Bob[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerC[/TD]
[TD]deployc3[/TD]
[TD]Bob[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerC[/TD]
[TD]deployc2[/TD]
[TD]Kelly[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerC[/TD]
[TD]deployc1[/TD]
[TD]Susie[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=SUM(IF(FREQUENCY(IF($B$2:$B$14<>"",IF($A$2:$A$14=A2,MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($B$2:$B$14)-ROW(B2)+1),1))

 

[Aladin Akyurek]

E1: Customer, F1: Sales

E2, control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF($A$1:$A$14<>"",
  IF($B$1:$B$14=E$1,MATCH($A$1:$A$14,$A$1:$A$14,0))),
  ROW($A$1:$A$14)-ROW($A$1)+1),1))

Hello, I have read this entire thread, and have a very similar situation as the others, but I can't seem to get my formula to work. I have two parts, and am stuck with part A still. Please I'd appreciate any help provided. Part A: identify number of unique deployments by Customer, and then Part B: to identify the number of unique contacts within each deployment for that Customer. Below is my formula, and I am having trouble copying it down and showing the correct results.

[...]

=SUM(IF(FREQUENCY(IF($B$2:$B$14<>"",IF($A$2:$A$14=A2,MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($B$2:$B$14)-ROW(B2)+1),1))

D1: # Unique deployments by customer

D2, control+shift+enter (CSE) and copy down:

=IFERROR(VLOOKUP($A2,$A$1:$D1,2,0),
  SUM(IF(FREQUENCY(IF($B$2:$B$14<>"",IF($A$2:$A$14=$A2,
  MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($B$2:$B$14)-ROW($B$2)+1),1)))

If a deployment cannot be deployed by two or more different customers...

E1: Unique contacts by deployment

E2, CSE and copy down:

=IFERROR(VLOOKUP($B2,$B$1:$E1,2,0),
  SUM(IF(FREQUENCY(IF($C$2:$C$14<>"",IF($B$2:$B$14=$B2,
  MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($C$2:$C$14)-ROW($C$2)+1),1)))

If a deployment can be deployed by two or more different customers...

E2, CSE and copy down:

=IFERROR(VLOOKUP($B2,$B$1:$E1,2,0),
  SUM(IF(FREQUENCY(IF($C$2:$C$14<>"",IF($A$2:$A$14=$A2,IF($B$2:$B$14=$B2,
  MATCH($B$2:$B$14,$B$2:$B$14,0)))),ROW($C$2:$C$14)-ROW($C$2)+1),1)))

 

[Beafernand]

D1: # Unique deployments by customer

D2, control+shift+enter (CSE) and copy down:

=IFERROR(VLOOKUP($A2,$A$1:$D1,2,0),
  SUM(IF(FREQUENCY(IF($B$2:$B$14<>"",IF($A$2:$A$14=$A2,
  MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($B$2:$B$14)-ROW($B$2)+1),1)))

If a deployment cannot be deployed by two or more different customers...

E1: Unique contacts by deployment

E2, CSE and copy down:

=IFERROR(VLOOKUP($B2,$B$1:$E1,2,0),
  SUM(IF(FREQUENCY(IF($C$2:$C$14<>"",IF($B$2:$B$14=$B2,
  MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($C$2:$C$14)-ROW($C$2)+1),1)))

If a deployment can be deployed by two or more different customers...

E2, CSE and copy down:

=IFERROR(VLOOKUP($B2,$B$1:$E1,2,0),
  SUM(IF(FREQUENCY(IF($C$2:$C$14<>"",IF($A$2:$A$14=$A2,IF($B$2:$B$14=$B2,
  MATCH($B$2:$B$14,$B$2:$B$14,0)))),ROW($C$2:$C$14)-ROW($C$2)+1),1)))

Hello, and THANK YOU! The formula worked wonderfully to produce unique deployments for specific number of rows (up to 58). How, my issue now is the number of rows 13358 I need to perform this calculation for. When I try to use the formula, just for one row identifying the range up 13358 rows, the excel gets hung up. So I created ranges for the 3 areas, the VLOOKUP range, the Customer range, and the Deployment range. Returned #NA value, and have not even tried to copy down with the array to the remaining rows. This is an issue still with the first row, just because my end row is 13358. Is there another type of range/array I need to create in order for excel to process through these number of rows?

=IFERROR(VLOOKUP($A2,$A$1:$D1,2,0),
SUM(IF(FREQUENCY(IF($B$2:$B$14<>"",IF($A$2:$A$14=$A2,
MATCH($B$2:$B$14,$B$2:$B$14,0))),ROW($B$2:$B$14)-ROW($B$2)+1),1)))