+ Reply to Thread
Results 1 to 11 of 11

COUNTIF, 2 conditions

  1. #1
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    COUNTIF, 2 conditions

    I have done a google search and found a couple articles discussing doing a countIF or count(if. However, I still can't use those concepts to get the numbers I need. I need to know how many new hires we had by week, and how many terms, as well as an overall headcount.

    Step one is I have a scorecard that has week beginning in column J.

    J3- 4/22/12
    J4- 4/29/12
    J5- 5/6/12
    J6- 5/13/12
    J7- 5/20/12
    J8- 5/27/12
    J9- 6/3/12
    J10- 6/10/12

    On 5/7 there was a new hire class of 13, and on 5/29/13 we added 27 more employees. There is a roster that has all of the employee's and their names as well as a class date (5/29/12 or 5/7/12 so far).

    What I was trying to do is count how many Class Dates are between J3 and J3+7 (0). However when J5 is the week value it should pull in 13. When J8 is used it should be 27, all other weeks should be 0. It's count range of class dates is '[Team Roster.xls]Agents'!$G:$G.

    Summary: Need count if class date ('[Team Roster.xls]Agents'!$G:$G) greater than or equal to week start, AND less than or equal to week start + 7 (Week start is column J). Hopefully this made sense, any and all help is greatly appreciated.

    My main area of confusion is I can put a count(if together for the criteria I am testing, but if both are true, what do I put for the true formula? When I put ('[Team Roster.xls]Agents'!$G:$G) it returns 40 on 5/27/12 and 1 on all other weeks.

    EDIT: This is my current formula:

    =COUNT(IF(AND('[Team Roster.xls]Agents'!$G$1:$G$3000>=J8,('[Team Roster.xls]Agents'!$G$1:$G$3000<=J8+7)),'[Team Roster.xls]Agents'!$G$1:$G$3000))

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: COUNTIF, 2 conditions

    Try with COUNTIF

    =COUNTIF('[Team Roster.xls]Agents'!$G$1:$G$3000,">="&J8)-COUNTIF('[Team Roster.xls]Agents'!$G$1:$G$3000,">"&J8+7)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: COUNTIF, 2 conditions

    That's perfect and it did the trick. Why do I take one minus the other?. In a lot of the documents I saw online they were adding, subtracting, or multiplying and I don't understand why. I would think I only want to count the number that meet both criteria, not meets one total minus meets other.

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: COUNTIF, 2 conditions

    Consider J8 = May/1/2012

    So 1st COUNTIF will count >=May/1/2012, let say the answer 10
    2nd COUNTIF will look >May/1/2012+7, which is >May/8/2012, let say the answer 3

    So 1st COUNTIF - 2nd COUNTIF, 10 - 3 = 7

  5. #5
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: COUNTIF, 2 conditions

    I have another problem and for the life of me I don't know what I am missing. In the table below I am trying SUMIF Week=27 and Form = Phone. Sum range Earned. So I should get 98.46.

    Month Week Form Evals Points Earned Points Possible Average Score
    Jun 26 Certification 2 194.13 200 97.1%
    Jun 26 Certification 2 198.73 200 99.4%
    Jun 26 Certification 2 186.57 200 93.3%
    Jun 26 Certification 2 197.3 200 98.7%
    Jun 26 Certification 2 197.61 200 98.8%
    Jun 26 Certification 1 98.57 100 98.6%
    Jul 27 Phone 1 98.46 100 98.5%
    Jun 26 Certification 2 200 200 100.0%
    Jul 27 Certification 2 190.88 200 95.4%
    Jun 25 Certification 1 100 100 100.0%
    Jun 26 Certification 2 198.77 200 99.4%
    Jul 27 Certification 2 193.77 200 96.9%
    Jul 27 Certification 2 188.57 200 94.3%
    Jul 28 Certification 2 193.75 200 96.9%
    Jul 28 Certification 2 186.29 200 93.1%
    Jun 26 Certification 2 200 200 100.0%
    Jul 29 Certification 2 200 200 100.0%
    Jul 29 Certification 2 186.83 200 93.4%
    Jun 26 Certification 1 100 100 100.0%
    Jun 26 Certification 1 98.82 100 98.8%
    Jun 26 Certification 2 197.53 200 98.8%
    Jun 26 Certification 2 196.1 200 98.1%
    Jun 26 Certification 2 197.47 200 98.7%
    Jul 29 Certification 2 177.66 200 88.8%
    Jul 29 Certification 2 200 200 100.0%
    Jul 29 Certification 2 188.31 200 94.2%


    Currently my formula is: =SUM(IF(AND('Agent Summary'!$G$1:$G$9000="Phone",'Agent Summary'!$F$1:$F$9000=WEEKNUM(F117)),'Agent Summary'!$I$1:$I$9000))

    SUMIF(AND(condition 1-Table above Range G:G which is form="Phone",table above range F which is week number= 27, sum range points earned.

    I have broken the formula down and it can find all of the week 27's which is 672 {=SUM(IF('Agent Summary'!$F$1:$F$9000=WEEKNUM(F117),'Agent Summary'!$I$1:$I$9000))}, it can find the points of 98.5 {=SUM(IF('Agent Summary'!$G$1:$G$9000="Phone",'Agent Summary'!$I$1:$I$9000))}

    So how do I combine them?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: COUNTIF, 2 conditions

    You get better help if you post an Excel-example of your workbook, without confidential information.
    Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
    Use BEFORE/AFTER sheets if that helps make it clearer.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: COUNTIF, 2 conditions

    [URL="https://www.dropbox.com/s/1ffnqi8o9khw7zv/SUMIF%20Help.xlsx"]

    The data table on the left is on my Agent Summary tab, I have two SUMIF's I need to combine. I need it to pull in only the data from Phone forms AND week 27. Right now there is only one Phone and it is on week 27 so if it is easier for the time being it can look up "Certification" by weeks. The purpose would be to see how many phone (or certification) points were earned on week 26, how many phone (or certification) points were possible on week 26, and divide. Ideally The dividing the points earned by points possible would take place in the same cell, but I can probably figure that out on my own.

    ---------- Post added at 05:37 PM ---------- Previous post was at 05:21 PM ----------

    I also ran into another issue, the formula above:
    =COUNTIF('[Team Roster.xls]Agents'!$G$1:$G$3000,">="&J8)-COUNTIF('[Team Roster.xls]Agents'!$G$1:$G$3000,">"&J8+7)
    only works when the workbook is open. I found this article http://www.excelforum.com/excel-gene...-workbook.html but I wasn't sure what was meant by actual ranges verse table references. Am I using table references?
    Last edited by ckk403; 07-23-2012 at 06:24 PM.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIF, 2 conditions

    COUNTIF doesn't work with closed workbooks, you can try SUMPRODUCT, i.e.

    =SUMPRODUCT((ABS('[Team Roster.xls]Agents'!$G$1:$G$3000-J8-3)<=3)+0)

    That will replicate your current formula, I'm not sure how you want to expand it, there's another criterion for another column?
    Audere est facere

  9. #9
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: COUNTIF, 2 conditions

    I am not sure what that formula does. I understand SUMPRODUCT
    =SUMPRODUCT((ABS('[Team Roster.xls]Agents'!$G$1:$G$3000-J8-3)<=3)+0)
    but taking the sum of the range G1:G3000 then subtracting the weeknum - 3 if greater <=3? I got lost.

    Hopefully this will clarify the help I am needing.

    The first concern is that this formula only works when the external workbook is open. What other options do I have?
    =COUNTIF('[Team Roster.xls]Agents'!$G$1:$G$3000,">="&L3)-COUNTIF('[Team Roster.xls]Agents'!$G$1:$G$3000,">"&L3+7)
    The formula is counting all of the people who started after L3 but before L3+7 which is a week start and end date (5/6/12, 5/12/12). It's an array formula so I press ctrl+Shift+enter. The G:G range in the Team Roster is a list of hirer dates.

    The second concern is I have a table (within this workbook so it will always be open) that has 11 columns:

    A- LAN ID
    B- Agent Name
    C- EE#
    D- Evaluation Date
    E- Month (Jun, Jul)
    F- Week Num (26, 27)
    G- Form (Certification, Phone)
    H- # of Evaluations
    I- Points Earned
    J- Points Possible
    K- Average Score (I divided by J)

    On a summary/score card tab I track the data on a weekly basis, so I need to know how many points earned the Phone form had on week 26, week 27, week 28..etc for a 6 week view. In the example above (the dropbox link) I have illustrated the ability to pull in all Phone form points earned, or all week 27 points possible, but I need a formula that pulls in criteria that meets both week 27 and a phone form. In the dropbox example it is real data (nothing confidential included) there is only one Phone form. In order to better test the data the certification form can be used to get the formula working and then I can switch it to phone. My current formula is

    =SUM(IF('Agent Summary'!$G$1:$G$9000="Phone",'Agent Summary'!$I$1:$I$9000))
    SumIF the points earned column if the form is = to "Phone". I can also adjust the formula above to

    =SUM(IF('Agent Summary'!$F$1:$F$9000=WEEKNUM($F117),'Agent Summary'!$I$1:$I$9000))
    which is Sum the points earned column if week number is 27 (Weeknum(F117) is converting week start date of 7/1/12 to 27. I hope all this helps.
    Last edited by ckk403; 07-24-2012 at 08:41 AM.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIF, 2 conditions

    In your last post you were using J8 and J8+7 so I assumed that J8 was the start date of the week (not the week number) , if L3 is the start date of the week then you can use the same formula that I proposed but with L3, i.e.

    =SUMPRODUCT((ABS('[Team Roster.xls]Agents'!$G$1:$G$3000-L3-3)<=3)+0)

    If you subtract the start date of the week [L3] from the dates then the result for the week of interest will be in the range 0 to 6, subtract 3 and that becomes -3 to 3 so any ABS value <=3 is valid, hence the above. More transparent (but longer)

    =SUMPRODUCT(('[Team Roster.xls]Agents'!$G$1:$G$3000>=L3)*('[Team Roster.xls]Agents'!$G$1:$G$3000< L3+7))

    both should give the same results and avoid using COUNTIF so can be used even when the source workbook is closed

    For your second query you can also use SUMPRODUCT (avoiding array entry) so to sum for "Phone" and week 27 try

    =SUMPRODUCT(('Agent Summary'!$F$1:$F$9000=WEEKNUM($F117))*('Agent Summary'!$G$1:$G$9000="Phone"),'Agent Summary'!$I$1:$I$9000))

    I note from your profile 2003/2010 - the above will work in either - in Excel 2010 SUMIFS would be better but that isn't available in Excel 2003

  11. #11
    Registered User
    Join Date
    12-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2003/2010
    Posts
    69

    Re: COUNTIF, 2 conditions

    The L3 and J8 difference was me adding columns since the first time I asked for help in this thread. I meant start date not week number sorry about the confusion on that. I did try to use the formula earlier (updating J8 to L8) and I don't know what I did wrong but it didn't work. I tried the one you just gave me and it works perfect. Both of them work fantastically. Thank you very much for putting this together for me as well as explaining the multiplications.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1