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.

 

[vxk5018]

Sorry I misspoke...there should only be 4 changeovers. from previous month size (assume minor) to 4 and then from 4 to 4, from 4 to 2, from 2 to 2 so 3 minor changeovers and 1 major changeover

 

[kx5470]

Team,


I was reading this but im trying to see if i can use this sample on my table.


What i need is basically to get this


[TABLE="width: 371"]
<tbody>[TR]
[TD]Dates[/TD]
[TD]Number of unique person[/TD]
[/TR]
[TR]
[TD]From 10/01/2014 to 10/05/2014[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]From 10/06/2014 to 10/09/2014[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]From 10/10/2014 to 10/15/2014[/TD]
[TD="align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]


But this comes from this raw data

[TABLE="width: 251"]
<tbody>[TR]
[TD="align: right"]10/1/2014[/TD]
[TD] Name[/TD]
[/TR]
[TR]
[TD="align: right"]10/2/2014[/TD]
[TD] Caba, Lewkys[/TD]
[/TR]
[TR]
[TD="align: right"]10/3/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/4/2014[/TD]
[TD] Caba, Lewkys[/TD]
[/TR]
[TR]
[TD="align: right"]10/5/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/6/2014[/TD]
[TD] Castillo, Ernesto[/TD]
[/TR]
[TR]
[TD="align: right"]10/7/2014[/TD]
[TD]Caba, Lewkys[/TD]
[/TR]
[TR]
[TD="align: right"]10/8/2014[/TD]
[TD]Caba, Lewkys[/TD]
[/TR]
[TR]
[TD="align: right"]10/9/2014[/TD]
[TD]Caba, Lewkys[/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2014[/TD]
[TD]Diazmaria, Brigida[/TD]
[/TR]
[TR]
[TD="align: right"]10/11/2014[/TD]
[TD]Castillo, Ernesto[/TD]
[/TR]
[TR]
[TD="align: right"]10/12/2014[/TD]
[TD]Castillo, Ernesto[/TD]
[/TR]
[TR]
[TD="align: right"]10/13/2014[/TD]
[TD]Difot, Michael[/TD]
[/TR]
[TR]
[TD="align: right"]10/14/2014[/TD]
[TD]Castilloabreu, Luis[/TD]
[/TR]
[TR]
[TD="align: right"]10/15/2014[/TD]
[TD]Castilloabreu, Luis[/TD]
[/TR]
[TR]
[TD="align: right"]10/16/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/17/2014[/TD]
[TD]Cortorreal, Yaraset[/TD]
[/TR]
[TR]
[TD="align: right"]10/18/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/19/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/20/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/21/2014[/TD]
[TD]Cordero, Nicolas[/TD]
[/TR]
[TR]
[TD="align: right"]10/22/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/23/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/24/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/25/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/26/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/27/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/28/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/29/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/30/2014[/TD]
[TD]Castro, Wanderemilio[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2014[/TD]
[TD]Cedeno, Jeiry[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2014[/TD]
[TD]Cedeno, Jeiry[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@kx5470

Let A2:B40 house the name data.

E2: 10/01/2014
F2: 10/05/2014

G2, control+shift+enter, not just enter, and copy down for other criterion dates:

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

 

[suzpa]

Hi Aladin, I hope you can help me with this. I've been trying to self-educate by following the formulas you've given to everyone else, but I can't seem to apply to my situation.

I have 2 worksheets - Payroll and MasterRep

In Payroll, A contains either of 2 values (2013 or 2014), F contains CostCentre Codes, L contains Employee IDs.
In MasterRep, A contains CostCentreCodes, and cell H2 contains the value 2013

I've been trying to write a formula in MasterRep that will tell me the headcount per cost centre in 2013.
I got as far as this
IFERROR(SUM(IF(FREQUENCY(IF('Payroll'!F2:F1586='MasterRep'!A5,'Ref - PayrollCS'!L2:L1586),'Ref - PayrollCS'!L2:L1586),1)),"-") which counts the number of unique employee ID for 2013 and 2014

I can't work out how to put in the second criteria, ie IF'Payroll'!A2:A1586='MasterRep'!H2 so that I only count for 2013.

 

[Aladin Akyurek]

Hi Aladin, I hope you can help me with this. I've been trying to self-educate by following the formulas you've given to everyone else, but I can't seem to apply to my situation.

I have 2 worksheets - Payroll and MasterRep

In Payroll, A contains either of 2 values (2013 or 2014), F contains CostCentre Codes, L contains Employee IDs.
In MasterRep, A contains CostCentreCodes, and cell H2 contains the value 2013

I've been trying to write a formula in MasterRep that will tell me the headcount per cost centre in 2013.
I got as far as this
IFERROR(SUM(IF(FREQUENCY(IF('Payroll'!F2:F1586='MasterRep'!A5,'Ref - PayrollCS'!L2:L1586),'Ref - PayrollCS'!L2:L1586),1)),"-") which counts the number of unique employee ID for 2013 and 2014

I can't work out how to put in the second criteria, ie IF'Payroll'!A2:A1586='MasterRep'!H2 so that I only count for 2013.

Looks like...

MasterRep, I2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H2,
  IF(Payroll!$F$2:$F$1586=$A5,IF(Payroll!$L$2:$L$1586<>"",
  MATCH(Payroll!$L$2:$L$1586,Payroll!$L$2:$L$1586,0)))),
  ROW(Payroll!$L$2:$L$1586)-ROW(Payroll!$L$2)+1),1))

 

[suzpa]

It worked! Thanks very much Aladin, you're such a great help!
Would you mind stepping me through what is happening (in non-technical terms) in the Match and Row parts of the formula?

 

[Aladin Akyurek]

It worked! Thanks very much Aladin, you're such a great help!

You are welcome.

Would you mind stepping me through what is happening (in non-technical terms) in the Match and Row parts of the formula?

1. FREQUENCY creates a count per bin.

FREQUENCY({1,3,1},{1,2,3})

==>

{2;0;1;0}

[i.e., 2x1, 0x2, 1x3, and nothing above 3]

2. Matching a range against itself creates the input for FREQUNCY...

MATCH({"a","b","a","c"},{"a","b","a","c"},0)

==>

{1,2,1,4}

[i.e., "a" of the first set matches "a" of the second set at position 1,
"b" of the first set matches "b" of the second set at position 2,
the second occurrence of "a" of the first set matches the first occurrence of "a" of the second set a position 1, and so on.]

3. MATCH is affected by the IFs round it. That is, MATCH matches the items of a range against itself when those IF allow for the matching (unallowed we get FALSE's in the MATCH result, not position numbers).

4. FREQUENCY creates a count of MATCH results per bin, where the bin set is given with ROW(...)-ROW(...)+1.

ROW(A2:A4)-ROW(A2)+1

==>

{1;2;3}

5. IF(FREQUNCY(...),1) creates for every non-zero bin count just a 1.

6. Summing 1's from the latter step yields the desired unique/distinct count.

Short recap. Map the conditioned MATCH results to the bins the row expression creates, then count every non-zero valued bin as 1 in order get a distinct count.

Hope this helps.

 

[suzpa]

Thanks very much!

 

[Aladin Akyurek]

Thanks very much!

You are welcome.

 

[suzpa]

Hi Aladin, me again...one last thing for this query.

What if I wanted to count unique employee ids for a group of cost centres in J2?
I.e. 2013 Headcount for Cost Centres 123 (A5) and 144 (A30) where both cost centres paid for mutual employees.

I assume it's a tweak in the second line of your formula by adding to the criteria Payroll!$F$2:$F$1586=$A5 but I don't know how to tweak it.

You help is most appreciated!