+ Reply to Thread
Results 1 to 6 of 6

I can't make this countif equation work. Help

  1. #1
    Registered User
    Join Date
    02-03-2016
    Location
    London, England
    MS-Off Ver
    mac 2011
    Posts
    5

    I can't make this countif equation work. Help

    Hi,

    I've been using excel for a long time. However i'm now learning a whole new level of it for my new job and i've hit a snag.

    I need to know how many times the courier was on time.

    This equation works well,

    =COUNTIFS(Colours!$D3:$D7,">08:15",Colours!$D3:$D7,"<08:25")

    However they have to arrive within a 10min window on lots of different times, for example,

    =COUNTIFS(Colours!$F3:$F7,">09:15",Colours!$F3:$F7,"<09:25")

    Instead of writing out the < and > times for each individual i was it to do it automatically when i drag down

    I tried doing this by e.g. having

    =COUNTIFS(Colours!$D3:$D7,">A1",Colours!$D3:$D7,"<B1")
    =COUNTIFS(Colours!$F3:$F7,">A2",Colours!$F3:$F7,"<B2")

    Then having a separate list those cells could correspond to which i could then hide later on. This would also make life easier if the courier times ever change in the future. However when ever i try that the result is always 0. What am i doing wrong? When ever i link cells else where it works fine.

    Thank you in advance for any help it is really appreciated.

    Holden

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,693

    Re: I can't make this countif equation work. Help

    If you do it that way then you must put the cell references outside the quotes, otherwise they will be considered as literal strings. Try it this way:

    =COUNTIFS(Colours!$D3:$D7,">"&A1,Colours!$D3:$D7,"<"&B1)

    Then when you copy this down the A1 and B1 will change to A2, B2 and so on. If the window is always 10 minutes, then you can avoid having to use column B, and make your formula this instead:

    =COUNTIFS(Colours!$D3:$D7,">"&A1,Colours!$D3:$D7,"<"&A1+TIME(0,10,0))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-03-2016
    Location
    London, England
    MS-Off Ver
    mac 2011
    Posts
    5

    Re: I can't make this countif equation work. Help

    Hi Pete,

    The first one works which is great. However the second one still gives the result 0 which is a shame as it seems a lot more efficient.

    Also

    Is there any way of making the equation go,

    =COUNTIFS(Colours!D$3:D$7,">"&A1,Colours!D$3:D$7,"<"&B1)
    =COUNTIFS(Colours!E$3:E$7,">"&A2,Colours!E$3:E$7,"<"&B2)
    =COUNTIFS(Colours!F$3:F$7,">"&A3,Colours!F$3:F$7,"<"&B3)

    I thought by having the $ by the number only it would lock the number and let the letter change by dragging.

    Thanks

    Holden

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,693

    Re: I can't make this countif equation work. Help

    Quote Originally Posted by Holden Wright View Post
    ... I thought by having the $ by the number only it would lock the number and let the letter change by dragging ...
    If you drag a formula containing a cell reference DOWN a column, then only the row number part would change, i.e. D7 would become D8, D9 and so on, and if you change the reference to D$7 and drag that down, then it will remain the same on successive rows. The column part of the reference would only change if you copy the formula ACROSS a particular row (but then if you did that to the above formula you would need to change the A1 and B1 to $A1 and $B1 to stop them changing the column reference, and the row reference would not change). What you need, then, is something that changes a column reference when it is copied down.

    Here is one way:

    =COUNTIFS(INDEX(Colours!$D$3:$Z$7,0,ROWS($1:1)),">"&A1,INDEX(Colours!$D$3:$Z$7,0,ROWS($1:1)),"<"&B1)

    This basically considers the 2-D array D3:Z7 on the Colours sheet and take those rows (i.e. from 3 to 7, due to the second parameter of the INDEX function being set to 0), but the column number is taken from the value of the ROWS($1:1) term. Initially this term returns the value 1, and so the first column of that array is used (i.e. D), but when the term is copied down it becomes ROWS($1:2) [returning 2 and thus the second column, or E], then ROWS($1:3) [thus returning 3, or the third column F], and so on. You can change the column Z (shown red in the above formula) to whichever column is your last column of data.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    02-03-2016
    Location
    London, England
    MS-Off Ver
    mac 2011
    Posts
    5

    Re: I can't make this countif equation work. Help

    You are a genius!

    Thank you very much.

    I have learnt a lot

    Holden

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,693

    Re: I can't make this countif equation work. Help

    Glad to be able to help.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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