Results 1 to 13 of 13
Thread: Formula Arrays (2003)

20070706, 18:56 #1
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula Arrays (2003)
Edited by HansV to present data in table format
I can't figure out an array formula for counting based on multiple criteria. I am playing with a mix of COUNTA and COUNTIF formulas, but can't seem to get it right. I want to count records that meet an Organization criteria, a Status criteria and a completion criteria (marked by "X)and others, but I'll figure those out after understanding the logic. Below is an example of a subset of the data. Essentially I am trying to pull a number of different counts of the data like "How many active Group A people have completed Class 3?".
For instance:
<table border=1><td>Name</td><td>Organization</td><td>Status</td><td>Class 1</td><td>Class 2</td><td>Class 3</td><tr><td>Joe</td><td>Group A</td><td>Active</td><td>X</td><td>X</td><td align=right>

20070706, 19:02 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
Welcome to Woody's Lounge!
For multiple conditions, you need a different kind of formula. SUMPRODUCT is often used for this. If your sample data are in A1:F5, the following formula will count the number of people in Group A who are active and who have completed Class 3:
=SUMPRODUCT(($B$2:$B$5="Group A")*($C$2:$C$5="Active")*($F$2:$F$5="X"))
See attached sample workbook.

20070706, 19:21 #3
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Arrays (2003)
Thanks Hans!
For the SUMPRODUCT function, does order matter at all? I'm trying to think of a situation where a count is made if an EITHER/OR condition is met.
For the F2:F5 range, what if I want it to count any value and not just "X" or count "X" and one other value like "Y"?

20070706, 19:40 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
The order of the arguments in SUMPRODUCT doesn't matter.
If you want to count any nonblank value in F2:F5, you can use
=SUMPRODUCT(($B$2:$B$5="Group A")*($C$2:$C$5="Active")*($F$2:$F$5<>""))
If you have an OR condition with mutually excluding criteria, you can use +:
=SUMPRODUCT(($B$2:$B$5="Group A")*($C$2:$C$5="Active")*(($F$2:$F$5="X")+($F$2:$F $5="Y")))
If you have overlapping conditions such as B2:B5 = "Group A" or F2:F5 = "X", you need yet another kind of formula  post back if you need that.

20070711, 16:26 #5
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Arrays (2003)
Hans,
I think that do have overlapping conditions it seems. I have attached a more useful example. It is a spreadsheet that tracking employees and their completion of training course. Sheet 2 has a table that I am trying to populate with equations. PivotTables are doing nothing but confuse me.
So I am wanting to count Active employees who have completed, been recognized or completed an alternate course within specified time frames based on when they came onboard. Using SUMPRODUCT has given me numbers that I know to be incorrect. Please take a look and populate a couple and see if I can take it from there.

20070711, 16:38 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
I inserted a column for the Org#. Here is a formula for the number of people in Org#1 onboard between .5 and 1 year:
=SUMPRODUCT((Sheet1!$B$2:$B$18=Sheet2!$B3)*(DATEDI F(Sheet1!$C$2:$C$18,TODAY(),"m")>6)*(DATEDIF(Sheet 1!$C$2:$C$18,TODAY(),"m")<=12))
More to come...

20070711, 16:47 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
See the attached workbook for the more complex formulas needed for the number of persons with complete etc. classes. I used an auxiliary column on Sheet1.

20070711, 17:23 #8
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Arrays (2003)
Okay! So the DATEDIF function is counting off of the actual Onboard Date using monthsvariable "m"? That simplifies things so I don't need to have the column J on Sheet 1. Isn't the "*" symbal for multiplying? Why wouldn't you use the "+" symbol in the equation?

20070711, 18:30 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
The DATEDIF function calculates the interval between two dates in the unit specified by the last argument. The result is always a whole number. We can't use years as units here, since you want to look at multiples of 0.5 years. That is not a whole number, so we use months as unit and look at multiples of 6 months.
DATEDIF is more accurate than dividing the number of days by 365, since DATEDIF takes leap years into account.
See DATEDIF function on the Microsoft site or DATEDIF Function on Chip Pearson's site for more info about DATEDIF.
* is indeed the multiplication operator. We use this because in Excel, TRUE corresponds to 1 and FALSE corresponds to 0. When you combine two conditions with AND, you get a 2by2 table as in the picture below. As you can see, the combination can be obtained by multiplying the individual values (1 = 1 * 1). That is why we use * in the SUMPRODUCT formulas.

20070712, 21:53 #10
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Arrays (2003)
Hans,
For some reason the function (DATEDIF(Sheet1!$C$2:$C$18,TODAY(),"m")<=6) if going beyond 6 months ago. It looks like it is actually counting records from the first half of 2007. I did an custom filter of the data and come with a different number. Is this a Microsoftism?
Also the function (Sheet1!$K$2:$K$18>0) that is supposed to be summing the record values of column K that meet the criteria as opposed to it just counting the number of records that have an integer greater than 0 in column K)
Please advise.

20070712, 22:14 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
1) The mistake is mine. If somebody has been "on board" for 6 months and 10 days, DATEDIF returns 6. We don't want to include this person in the group <0.5 years. So I should have used <6 instead of <=6, and the rest of the formulas should be changed accordingly.
2) There are only 2 persons who don't have any Recognized, Complete or Alternate result (Ravani, Scott and Myster, Scott). These are correctly excluded from the count in E2 on Sheet2: this cell contains 2 while the total number of persons in this group in cell D2 is 4. So the Sheet1!$K$2:$K$18>0 bit works as intended.

20070713, 18:55 #12
 Join Date
 Jul 2007
 Location
 United States
 Posts
 220
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Arrays (2003)
I made the changes that you recommended. However, I would like the counts in column K to be summed as opposed to just a count of the records that meet that criteria. That is, if an employee has been onboard in the certain time frame, then tally all of the classes (Completed, Recognized, Alternate) that they have completed.

20070713, 19:56 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Arrays (2003)
That is relatively easy: change (Sheet1!$K$2:$K$18>0) to Sheet1!$K$2:$K$18, for example in cell E3 on Sheet2:
<code>
=SUMPRODUCT((Sheet1!$B$2:$B$18=Sheet2!$B3)*(DATEDI F(Sheet1!$C$2:$C$18,TODAY(),"m")<6)*Sheet1!$K$2:$K $18)
</code>
Modified version attached.