Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    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>

  2. #2
    Plutonium Lounger
    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.

  3. #3
    3 Star Lounger
    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"?

  4. #4
    Plutonium Lounger
    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 non-blank 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.

  5. #5
    3 Star Lounger
    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.

  6. #6
    Plutonium Lounger
    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...

  7. #7
    Plutonium Lounger
    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.

  8. #8
    3 Star Lounger
    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 months--variable "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?

  9. #9
    Plutonium Lounger
    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 2-by-2 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.

  10. #10
    3 Star Lounger
    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.

  11. #11
    Plutonium Lounger
    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.

  12. #12
    3 Star Lounger
    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.

  13. #13
    Plutonium Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •