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, 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!

Since you are copying down the formula, what happens with A5 and A30? Do they become A6 and A31?

 

[suzpa]

No i won't be copying down. It will be a standalone cell.

 

[Aladin Akyurek]

No i won't be copying down. It will be a standalone cell.

In that case, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H$2,
  IF(ISNUMBER(MATCH(Payroll!$F$2:$F$1586,CHOOSE({1,2},$A$5,$A$30),0)),
  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]

In that case, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H$2,
  IF(ISNUMBER(MATCH(Payroll!$F$2:$F$1586,CHOOSE({1,2},$A$5,$A$30),0)),
  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))

I just realised that I have to reference the level above the cost centre instead, which is an alphanumeric value. I swapped the cost centre positions in your formula with the positions of those values, i.e. K1 = F2CY and K2=F2IR but it didnt work. Is it because the alphanumeric violates the ISNUMBER part?

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H$2,
IF(ISNUMBER(MATCH(Payroll!$F$2:$F$1586,CHOOSE({1,2},$K$1,$K$2),0)),
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))

By the way some of the employee IDs in Payroll column L also have an alphabet attached, eg. 22304A.

 

[Aladin Akyurek]

I just realised that I have to reference the level above the cost centre instead, which is an alphanumeric value. I swapped the cost centre positions in your formula with the positions of those values, i.e. K1 = F2CY and K2=F2IR but it didnt work. Is it because the alphanumeric violates the ISNUMBER part?

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H$2,
IF(ISNUMBER(MATCH(Payroll!$F$2:$F$1586,CHOOSE({1,2},$K$1,$K$2),0)),
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))

By the way some of the employee IDs in Payroll column L also have an alphabet attached, eg. 22304A.

1. If the criteria/conditions are in a contiguous range like K1:K2, there will be no need for CHOOSE.

2. ISNUMBER tests MATCH results and picks out the numbers and skips possible #N/A's. Note that MATCH returns either a matching position as integer and returns #N/A when no match is found.

3. MATCH can compare/match text values and numbers like 10, AB, 1Kad23, etc.

4. When you edit a formula that needs control+shift+enter, it must be again confirmed with control+shift+enter.

With K1:K2, control+shift+enter:

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H$2,
  IF(ISNUMBER(MATCH(Payroll!$F$2:$F$1586,$K$1:$K$2,0)),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))

The criteria in K1:K2 must occur as is in the F range. If they occur as part of an F entry, the MATCH skips such occurrences.

 

[suzpa]

1. If the criteria/conditions are in a contiguous range like K1:K2, there will be no need for CHOOSE.

2. ISNUMBER tests MATCH results and picks out the numbers and skips possible #N/A's. Note that MATCH returns either a matching position as integer and returns #N/A when no match is found.

3. MATCH can compare/match text values and numbers like 10, AB, 1Kad23, etc.

4. When you edit a formula that needs control+shift+enter, it must be again confirmed with control+shift+enter.

With K1:K2, control+shift+enter:

=SUM(IF(FREQUENCY(IF(Payroll!$A$2:$A$1586=$H$2,
  IF(ISNUMBER(MATCH(Payroll!$F$2:$F$1586,$K$1:$K$2,0)),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))

The criteria in K1:K2 must occur as is in the F range. If they occur as part of an F entry, the MATCH skips such occurrences.

Hi Aladin, fantastic it worked! Yes it was a contiguous range for K. And the values in K don't occur in the F range - it's a different range, so I just changed the reference from F to that other range.
Thanks again for your patience and help!

 

[Aladin Akyurek]

Hi Aladin, fantastic it worked! Yes it was a contiguous range for K. And the values in K don't occur in the F range - it's a different range, so I just changed the reference from F to that other range.
Thanks again for your patience and help!

You are welcome.

 

[Satele]

Hi Aladin,

I've posted around in other places looking for help but can't get anywhere. I have a couple of problems similar to the ones in this thread which I can't get my head around using the solutions you've already posted. I've been at this for a couple of weeks now.

I have two sheets in my workbook: Data and Report

The Data sheet is set up as follows:

A B C D E F
90010 Admission HospA 29/11/2012 01/04/2013 05/05/2013
90010 Discharge HospA 30/11/2012 07/01/2012 02/01/2013
90011 Admission HospA 12/04/2014 08/12/2013 13/07/2013
90013 Discharge HospA 04/03/2013 27/07/2013 29/12/2013

The data extends down to no more than row 50000 so my range would be 2:50000

There are more columns than this, but these are the those which I need to the formula to query.

On my 'Report' sheet, I want to query the Data sheet to produce the following in cell D2, where C2 contains the Hospital code ('HospA' in this case) found in column C of the 'Data' sheet. I'd like the formula to be 'dynamic' so to speak, whereby when the hospital code in C2 of the report sheet changes, the formula recalculates based on this.

So, in summary, the formula should evaluate: the number of records (rows) where column B of the Data sheet equals ‘Admission’, column C equals the value in 'Report!C2', column D is not equal to blank (i.e. no value in cell), column E is between 01/04/2014 and 31/03/2013, and where the value in Column A only occurs once in the column.

The value in column A is unique to each new admission, and any subsequent entries for that record will have the same value in column A. Basically, what I am trying to calculate is the number of Admissions which do not have any additional records to that unique number.

Any assistance with this would be immensely appreciated!

 

[Aladin Akyurek]

@Satale

Effecting a distinct count under multiple conditions involving 50,000 records is theoretically no problem, but not doable practically, even if we trade off memory against speed. You should probably try coding in VBA or adopting an SQL approach.

 

[Satele]

@Satale

Effecting a distinct count under multiple conditions involving 50,000 records is theoretically no problem, but not doable practically, even if we trade off memory against speed. You should probably try coding in VBA or adopting an SQL approach.

Thats disappointing. Is there something different about what I'm trying to do compared to the other examples here? I really wouldn't have a clue where to start with VBA/SQL for this problem.

For a separate issue, I've managed to use SUM(IF in an array and SUMPRODUCT to count multiple conditions using the same 50,000 rows. My main difficultly here is trying to only count these conditions alongside the unique values specified in column A.

Even if it it would be slow, I'd appreciate help in giving it a shot! I'm out of other options.