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.

 

[Kevin SantaBarbara]

I'm using the spreadsheet that you posted on page 5, but pasted my data in. https://dl.dropboxusercontent.com/u/...Christian.xlsx
I think my data set is too large for this approach (sigh...)

 

[Aladin Akyurek]

I'm using the spreadsheet that you posted on page 5, but pasted my data in. https://dl.dropboxusercontent.com/u/...Christian.xlsx
I think my data set is too large for this approach (sigh...)

Clicking on the link ends up in error...

 

[Kevin SantaBarbara]

Sorry, it wasn't supposed to be a link, just the text from page 5 of this thread with the link that someone put there (thought it was you...maybe not). Anyway, my point is that the exact formula that works for this kind of thing runs excel out of resources for a data set as large as mine. I wonder if there is a VBA way to do the same thing as a macro function?

 

[Kevin SantaBarbara]

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

I think this might be the link's entire URL.

 

[Kevin SantaBarbara]

Just verified that link works. In that file, imagine if there were 41000 unique Employee IDs in 500,000 rows in Column A with 136 unique Departments in 500,000 rows in Column B. Then array formula spins and spins like the job is too big for Excel/my computer (can't tell which).

Note: There are a bunch of repeat combinations of EmployeeID and Dept, but I only want to know (for now) HOW many Employee IDs in each Department. For my actual data set, I have a third column I'm interested in too, but if the formula worked on the first two, I could manage the third.

 

[Aladin Akyurek]

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

I think this might be the link's entire URL.

Just verified that link works. In that file, imagine if there were 41000 unique Employee IDs in 500,000 rows in Column A with 136 unique Departments in 500,000 rows in Column B. Then array formula spins and spins like the job is too big for Excel/my computer (can't tell which).

Note: There are a bunch of repeat combinations of EmployeeID and Dept, but I only want to know (for now) HOW many Employee IDs in each Department. For my actual data set, I have a third column I'm interested in too, but if the formula worked on the first two, I could manage the third.

I don't think you can modify the file I provided and upload it back.

A range of 500,000 rows is indeed big. Have a look at solutions that runs SQL on data in Excel as Fazza, a user of thos board, auggested a few times..

 

[tazflerts]

How do I count unique value in column A with criteria:

1 = Column B >= May 01, 2014
2 = Column B <= May 18, 2014

Dropbox file: https://www.dropbox.com/s/jnrxnb0a40ldotk/Tazflerts%20Sample%20001.xlsx

Thanks.

 

[Aladin Akyurek]

How do I count unique value in column A with criteria:

1 = Column B >= May 01, 2014
2 = Column B <= May 18, 2014

Dropbox file: https://www.dropbox.com/s/jnrxnb0a40ldotk/Tazflerts%20Sample%20001.xlsx

Thanks.

Control+shift+enter, not just enter:

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

where C1 houses 1-May-14 and C2 18-May-14 (with C1 <= C2).

 

[Aladin Akyurek]

I don't think you can modify the file I provided and upload it back.

A range of 500,000 rows is indeed big. Have a look at solutions that runs SQL on data in Excel as Fazza, a user of thos board, auggested a few times..

Would you also have a look at the following set up?

[TABLE="width: 576"]
<TBODY>[TR]
[TD="class: xl67, width: 125, bgcolor: white"]EmployeeID
[/TD]
[TD="class: xl67, width: 138, bgcolor: white"]Dept
[/TD]
[TD="class: xl67, width: 150, bgcolor: white"]0
[/TD]
[TD="class: xl68, width: 25, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 118, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, width: 213, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172705497
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]1000
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"]1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 118, bgcolor: white"]Dept List
[/TD]
[TD="class: xl67, width: 213, bgcolor: white"]Count Distinct ID's
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172705497
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]1000
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"]1000
[/TD]
[TD="class: xl65, width: 213, bgcolor: white"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172717693
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]4000
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"]2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"]4000
[/TD]
[TD="class: xl65, width: 213, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172717693
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]4000
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"]1100
[/TD]
[TD="class: xl65, width: 213, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172724808
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]1100
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"]3
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 213, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172737704
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]1000
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 213, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125, bgcolor: white"]172744009
[/TD]
[TD="class: xl65, width: 138, bgcolor: white"]1000
[/TD]
[TD="class: xl65, width: 150, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 118, bgcolor: white"][/TD]
[TD="class: xl65, width: 213, bgcolor: white"][/TD]
[/TR]
</TBODY>[/TABLE]

C1 must house a 0.

C2, just enter and copy down:

=IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),"",LOOKUP(9.99999999999999E+307,$C$1:C1)+1)

E1, just enter:

=LOOKUP(9.99999999999999E+307,C:C)

E3, just enter and copy down:

=IF(ROWS($E$3:E3)<=$E$1,LOOKUP(ROWS($E$3:E3),C:C,B:B),"")

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

=IF($E3="","",SUM(IF(FREQUENCY(IF(Dept=$E3,EmpID),EmpID),1)))

where, like before, Dept is defined as:

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

and EmpID as:

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

Note. Employee id's are assumed to be numbers.

See: https://dl.dropboxusercontent.com/u...bara ConditionalUniqueCount DynamicSetUp.xlsx

Does the foregoing deliver an acceptable efficiency score?

 

[tazflerts]

Many thanks.