I am wanting to return the number of employees(Col A), which meet two criterias(type((Col B))&location((Col C)).
Example result: "Sales" for "Little Rock" had 2 unique employees, Jan and Bob, regardless of how many times Jan or Bob were listed with these criteria.Uniquevaluecountifs.xlsx
D1:
This is an Array Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Please Login or Register to view this content.
Once the formula has been successfully entered, copy downwards.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Try this...
Data Range
This array formula** entered in G1:
=SUM(IF(FREQUENCY(IF((B1:B13=E1)*(C1:C13=F1),MATCH(A1:A13,A1:A13,0)),ROW(A1:A13)-ROW(A1)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I have tried to input this formula as my own but cannot make it work. Could you please proof?(i did hit ctrl shift enter)
=SUM(IF(FREQUENCY(IF(('[Ajera Info.xlsx]Employee Data'!$I2:$I75000=D22)*('[Ajera Info.xlsx]Employee Data'!$G2:$G75000=AL1),MATCH('[Ajera Info.xlsx]Employee Data'!$A2:$A75000,'[Ajera Info.xlsx]Employee Data'!$A2:$A75000,0)),ROW('[Ajera Info.xlsx]Employee Data'!$A2:$A75000)-ROW('[Ajera Info.xlsx]Employee Data'!$A$2)+1),1))
Cell D22 is a city name and AL1="*"&G1&"*", where G1 is used as a search box for the user. Currently AL1 is used as pivot filter using VBA and works well for that.
On 75k rows of data this formula will be slow to calculate.
Maybe this...
=SUM(IF(FREQUENCY(IF(('[Ajera Info.xlsx]Employee Data'!$I2:$I75000=D22)*(ISNUMBER(SEARCH(AL1,'[Ajera Info.xlsx]Employee Data'!$G2:$G75000))),MATCH('[Ajera Info.xlsx]Employee Data'!$A2:$A75000,'[Ajera Info.xlsx]Employee Data'!$A2:$A75000,0)),ROW('[Ajera Info.xlsx]Employee Data'!$A2:$A75000)-ROW('[Ajera Info.xlsx]Employee Data'!$A$2)+1),1))
Still array entered.
I have not been able to make this work. I will continue to look at it. Thanks for the help.
I'm guessing this is where the problem is...
You said:
What exactly does that mean?...AL1="*"&G1&"*"
What's in G1 and what's in AL1?
Your sample file doesn't demonstrate/correspond to this requirement.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks