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

EmployeeID	Dept			Dept List	Count Distinct ID's
172705497	1000			1000	3
172705497	1000			4000	1
172717693	4000			1100	1
172717693	4000
172724808	1100
172737704	1000
172744009	1000

<TBODY>
</TBODY>

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...)

A	B	C	D
Jessica Summers	Customer	2014-01-01	01-2014
Jessica Summers	Customer	2014-01-01	01-2014
Jessica Summers	Customer	2014-01-01	01-2014
Jessica Summers	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014

<tbody>
</tbody>

 

[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...)

A	B	C	D
Jessica Summers	Customer	2014-01-01	01-2014
Jessica Summers	Customer	2014-01-01	01-2014
Jessica Summers	Customer	2014-01-01	01-2014
Jessica Summers	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Doe	Customer	2014-01-01	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014
John Smith	Sales	2014-01-15	01-2014

<tbody>
</tbody>

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.

A Customer	B Deployment	C Contacts
CustomerA	deploya1	Harry
CustomerA	deploya1	Susie
CustomerA	deploya2	Harry
CustomerA	deploya3	Harry
CustomerB	deployb1	Frank
CustomerB	deployb1	Frank
CustomerB	deployb2	Jerry
CustomerB	deployb1	Jerry
CustomerB	deployb3	Susie
CustomerB	deployb3	Harry
CustomerC	deployc3	Bob
CustomerC	deployc3	Bob
CustomerC	deployc2	Kelly
CustomerC	deployc1	Susie

<tbody>
</tbody>

=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)))