+ Reply to Thread
Results 1 to 4 of 4

Count +1 for Each Affirmative Criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    08-09-2006
    Posts
    4

    Count +1 for Each Affirmative Criteria

    I have a spreadsheet that looks like the following:

    --------A---------B----------C-----------D----------E--------F-----------G----------H------------I---------J
    1--TimeMon--TimeTues--TimeWed---TimeThurs---TimeFri---MonMode---TuesMode--WedMode---ThursMode--FriMode
    2--9:00 am---9:00 am----5:00 am---10:00 am---11:00 am-----A-----------A---------A------------M---------M
    3
    4

    I need to count all instances "where TimeMon is between 6 am and 10 am and MonMode = A", "where TimeTues is between 6 am and 10 am and TuesMode = A", etc. for all rows in the spreadsheet.

    I can handle "between 6 am and 10 am" and using countif to count all "MonMode=A", but I can't wrap my head around counting all instances where the two criteria are true for Monday - Friday for all rows.

    Can this be done?

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,818

    Re: Count +1 for Each Affirmative Criteria

    =COUNTIFS(A:A,">="&TIME(6,0,0),A:A,"<="&TIME(10,0,0)), F:F, "A")
    That would count the monday - now if you want to add all the columns you would repeat the above for tue/wed/thu/fri and use a + to add them up
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    08-09-2006
    Posts
    4

    Thumbs up Re: Count +1 for Each Affirmative Criteria

    =COUNTIFS(A:A,">="&TIME(6,0,0),A:A,"<="&TIME(10,0,0)), F:F, "A")

    It works!!! I only had to pull out an extra parenthesis and it works!!

    Where's the "send beer" button?

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,818

    Re: Count +1 for Each Affirmative Criteria

    copy and paste for you
    thanks for the beer offer
    and the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count of rows in an array that meet criteria in column 2 and different criteria in column3
    By reynoldslarry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2013, 01:51 AM
  2. Count statement wtih multiple criteria only showing results for one criteria
    By uhlabomber in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2013, 02:47 PM
  3. Replies: 9
    Last Post: 06-25-2013, 09:00 AM
  4. Count till met criteria, reset counter and count further
    By Romas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2011, 01:42 PM
  5. Replies: 5
    Last Post: 09-22-2009, 06:11 PM

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