I am trying to write a formula but with no success yet. I have a range of time data that I want to write a COUNTIF formula for which will give me the number of times a time range was under 07:00 (H:mm). I wrote the following below but no success. Any help please?
COUNTIF('ED Data'!AI2:AI7, " < =" 'ED Data'!AK2) where ED Data is where the range is in another worksheet. I for some reason could not enter the criteria of 07:00 thus I defined the cell AK2 as being 07:30 (H:mm).
Thanks in advance
Last edited by KatMat232; 07-07-2017 at 04:25 PM.
Please any help?
Hi,
Might be better to post a workbook, but anyway:
Where AK2 contains 7:00 (why 7:30?)
Please Login or Register to view this content.
7:30 was a typo. I just edited it. I will try your formula and let you know. THank youOriginally Posted by Jdevil
Hi,
Might be better to post a workbook, but anyway:
Where AK2 contains 7:00 (why 7:30?)Please Login or Register to view this content.
You can remove the need for your extra cell using this:
Formula:
Please Login or Register to view this content.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Thanks JDevil - it works!! Curious though instead of having the AK2 as a place order for the 7:00, is there a way I can introduce the 7:00 time into the formula?Hi,
Might be better to post a workbook, but anyway:
Where AK2 contains 7:00 (why 7:30?)Please Login or Register to view this content.
Thank you Aardig - just saw your post as I replied and it works also. THank you both.You can remove the need for your extra cell using this:
Formula:Please Login or Register to view this content.
One more question, can this type of formula be inserted into a Pivot Table via a Calculated Field? I have tried this originally and nothing worked which is why I went the manual route.
I'm not good with pivot tables, but from what I can see, it's difficult to do a CountIf directly. What you could do is put another column next to your times with an IF check in it, something like IF(AI2<=TIME(7,0,0),1,0). Then pivot that and sum the If column. I'm not sure if that makes sense, so attached is a file which hopefully shows what I mean.
Last edited by Aardigspook; 07-07-2017 at 05:55 PM.
Okay. Will try. Thank you.I'm not good with pivot tables, but from what I can see, it's difficult to do a CountIf directly. What you could do is put another column next to your times with an IF check in it, something like IF(AI2<=TIME(7,0,0),1,0). Then pivot that and sum the If column. I'm not sure if that makes sense
Btw if anybody has any other suggestions, please share. Thanks one and all
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks