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

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

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

Originally Posted by Holden Wright

... 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

You are a genius!

Thank you very much.

I have learnt a lot

Holden

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