Results 1 to 14 of 14

20030327, 20:17 #1
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Complex Counting Problem (Excel 2000)
I have a rather complex counting problem I need a bit of help with.
Each month I export from Access to Excel the daily productivity records for each employee for the previous month.
Currently I

20030328, 00:22 #2
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Hi Hans,
I'm actually trying to automate the process as much as possible for other users, who know very little about Access and Excel, which is why I'm going this direction. I'm actually displaying a good deal of the info in a Pivot table, but because of the way it's structured and the other formulas that are calculating data from the Pivot table resutls I can't calculate what I need, as described below. I figure there's probably a way to do this, but I'm still learning how to write more complex formulas.
Thanks for your help.
Scott

20030328, 00:28 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Complex Counting Problem (Excel 2000)
Since you mention that the data are exported from Access, wouldn't it be easier to set up a few group by queries in Access and export those?
In Excel, a pivot table based on the table exported from Access might be just the ticket to do this.

20030330, 12:57 #4
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Care to attach a downsized sample of your worksheet?
Microsoft MVP  Excel

20030331, 13:07 #5
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Hi Aladin,
I slimed the file down to less than 100k. I'm counting the date in worksheet UF_Jan!E1, employees in UF_Jan!E2 and I have a work around formula in UF_Jan!E3, but since it's not dynamic, it would need to be edited as employee staffing by group changes. Ideally, I'd like to have a formulas similar to the ones E1 & E2; one that counts just the number of employees from Group 1 and another that counts just the number of employees in Group 2. I would then reference these cells in current formula in E3.
Thanks for your help.
Scott

20030331, 19:15 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Complex Counting Problem (Excel 2000)
Enter these ARRAY formulas
For Group 1:
=SUM(IF((LEN(UF_Data!NAME_UF_JAN)>0)*(UF_Data!GROU P_UF_JAN="Group 1"),1/COUNTIF(UF_Data!NAME_UF_JAN,UF_Data!NAME_UF_JAN)))
For Group 2
=SUM(IF((LEN(UF_Data!NAME_UF_JAN)>0)*(UF_Data!GROU P_UF_JAN="Group 2"),1/COUNTIF(UF_Data!NAME_UF_JAN,UF_Data!NAME_UF_JAN)))
Instead of "Group 1" and "Group 2" "hardcoded in you could reference a cell or a range name.
Steve

20030331, 21:02 #7
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Steve
You the man! It worked like a champ AND I only needed to use one formula because I referenced the value as a part of my pivot table selection.
Thanks!
Scott

20030401, 10:56 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Scott,
Your UF_Data appears to consist of dates solely in Jan 03. Is this also the case in the larger workbook?
AladinMicrosoft MVP  Excel

20030401, 16:16 #9
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Hi Aladin,
Actually Steve provided a solution for my problem that works great, so problem solved!
But to answer your question, yes, there would be a named range for each month.
Thanks for your offer to help.
Scott

20030401, 17:08 #10
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Scott,
I'm aware of Steve's contribution. I'm afraid those formula's are a bit risky to use for conditional unique count.
What I was asking is whether you have any other dates (e.g. February dates) in the date column than just January dates. If any date can be there, is the data sorted on the date column?
AladinMicrosoft MVP  Excel

20030402, 14:10 #11
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Aladin,
By "risky", do you mean unreliable? It seems to be working fine.
To your question. Yes there will be data sets for each month. I've attached another to better illustrate.
Thanks again for your help.
Scott

20030402, 14:15 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Could you explain what you mean by risky?
Legare Coleman

20030402, 15:19 #13
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
A bit history
[1]
{=SUM(1/COUNTIF(Range,Range))}
which is due to David Hager.
[2]
{=SUM(IF(LEN(Range),1/COUNTIF(Range,Range)))
LEN addition by me in order to eliminate the problem due to the empty cells or cells housing formulas that return "".
These arrayformulas no doubt the best to compute a unique items count wrt a definite range.
Conditional unique count is a different matter...
Let A2:B8 house
{"a",1;"a","";"",1;"b",2;"c",1;"b",2;"d",3}
where "" stands for an empty cell or for a formula cell generating "".
In D2 enter: 1
In E2 arrayenter:
=SUM(IF((LEN(A2:A8)>0)*(B2:B8=D2),1/COUNTIF(A2:A8,A2:A8)))
It returns 1.5.Microsoft MVP  Excel

20030406, 06:50 #14
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Counting Problem (Excel 2000)
Scott,
As the attached file shows, UFTable is defined (using InsertNameDefine) as referring to:
=UF_Data!$A$2:INDEX(UF_Data!$F:$F,Admin!$B$2)
B2 in Admin (an additional worksheet) houses the following formula:
=MATCH(9.99999999999999E+307,UF_Data!A:A)
Note 1. Although column A houses dates, its underlying format was not numeric. Now it is. The forgoing MATCH formula requires that change.
Sheet UF_Jan (This sheet also has a summary on Feb: either change the name or move the Feb summary to a UF_Feb sheet)...
The arrayformula in D3...
=MIN(IF(TEXT(INDEX(UFTable,0,1),"mmmyyyy")=D$2&$D$ 1,ROW(INDEX(UFTable,0,1)),""))
computes the start row of the Jan 2003 data.
The arrayformula in D4...
=MAX((TEXT(INDEX(UFTable,0,1),"mmmyyyy")=D$2&$D$1) *ROW(INDEX(UFTable,0,1)))
computes the end row for the same data.
Since we need COUNTDIFF for conditional unique count, this function is also used for nonconditional unique counts:
The ordinary formula in D5...
=COUNTDIFF(INDEX(UF_Data!$A:$A,D$3,1):INDEX(UF_Dat a!$A:$A,D$4,1))
counts distict January dates in UF_Data.
The ordinary formula in D6...
=COUNTDIFF((INDEX(UF_Data!$B:$B,D$3,1):INDEX(UF_Da ta!$B:$B,D$4,1)))
counts distinct January employees.
The arrayformula in D7 for conditional unique count...
=COUNTDIFF(IF((INDEX(UF_Data!$C:$C,D$3,1):INDEX(UF _Data!$B:$B,D$4,1))=$C7,(INDEX(UF_Data!$B:$B,D$3,1 ):INDEX(UF_Data!$B:$B,D$4,1))))1
counts distinct January employees in Group 1.
This formula is copied down for other groups.
Note 2. In order to use COUNTDIFF, you need to download and install the morefunc.xll addin from:
http://longre.free.fr/english/index.html.
Note 3. The foregoing is set up with dynamic name ranges. If all this is too much hassle or not really needed, you can replace the INDEX bits that specify ranges with definite ranges or with names that refer to definite ranges (like your DATE_UF_JAN, etc.).
AladinMicrosoft MVP  Excel