Question description:
This user has given permission to use the problem statement for this
blog.
so there is a list of names, an amount of time next to each name and a date this name and time were entered. so i have a formula that counts the times a unique name appears in the list, i want it to count between whatever dates i specify
Solved by T. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/03/2018 - 02:02
Welcome to excel got it pro.
Excelchat Expert
18/03/2018 - 02:02
I'm here to help you today.
Excelchat Expert
18/03/2018 - 02:02
I'm fine, how about you?
Excelchat Expert
18/03/2018 - 02:02
Would you please show me some sample data?
User
18/03/2018 - 02:02
ok so let me put something
Excelchat Expert
18/03/2018 - 02:03
Thanks.
User
18/03/2018 - 02:07
hang on not finished
Excelchat Expert
18/03/2018 - 02:07
sure, I'm waiting. .
User
18/03/2018 - 02:08
one moment ill put the formula
Excelchat Expert
18/03/2018 - 02:09
Okay, no worries. I'm here.
Excelchat Expert
18/03/2018 - 02:12
still waiting...
User
18/03/2018 - 02:13
D1 is what i have in my sheet but i use excel
User
18/03/2018 - 02:13
n you change it to the array you would use in google sheets so i can complete the example
User
18/03/2018 - 02:13
can you change
Excelchat Expert
18/03/2018 - 02:14
Would you please specify again what is it you are trying to achieve?
User
18/03/2018 - 02:14
that would confuse you until i finish the example
User
18/03/2018 - 02:14
i think D1 need to have array something at the start to work in google sheets
Excelchat Expert
18/03/2018 - 02:15
Okay, the formula confused me already, the countif isn't correct.
User
18/03/2018 - 02:15
as i said its for excel
User
18/03/2018 - 02:15
not sheets
Excelchat Expert
18/03/2018 - 02:15
even for excel
Excelchat Expert
18/03/2018 - 02:15
its the same.
User
18/03/2018 - 02:15
the formula returns the unique names in A:A
User
18/03/2018 - 02:15
no its not
User
18/03/2018 - 02:15
for sheets it has array in front
User
18/03/2018 - 02:16
and not bracers
Excelchat Expert
18/03/2018 - 02:16
I meant the countif formula is the same as excel.
Excelchat Expert
18/03/2018 - 02:16
Okay, so you can't return unique names in that way.
User
18/03/2018 - 02:16
the whole formula doesn't work in sheets because its written for excel
User
18/03/2018 - 02:16
yes i can and i do!
User
18/03/2018 - 02:16
t works in excel
User
18/03/2018 - 02:17
i use it perfectly
User
18/03/2018 - 02:17
thats not the issue
Excelchat Expert
18/03/2018 - 02:17
okay.
Excelchat Expert
18/03/2018 - 02:17
lets hear the issue then.
User
18/03/2018 - 02:17
if i can finish the example you will understand
Excelchat Expert
18/03/2018 - 02:17
Take your time to finish, I'm here.
User
18/03/2018 - 02:17
I'm trying to but you keep getting stuck on this formula
User
18/03/2018 - 02:17
let me finish putting things in
User
18/03/2018 - 02:17
im not done
User
18/03/2018 - 02:19
let me finish
Excelchat Expert
18/03/2018 - 02:19
sure, take your time.
User
18/03/2018 - 02:23
ok so E:E counts the number of times the name in D:D appears in A:A do you understand so far?
Excelchat Expert
18/03/2018 - 02:23
Yup, no problem with it.
Excelchat Expert
18/03/2018 - 02:24
Though is has a problem and I will tell you about it later.
User
18/03/2018 - 02:24
so what i want that formula in E:E to do is give me a count of the times a name in D:D appears between certain dates i specify in G:G
User
18/03/2018 - 02:24
there is no problem
Excelchat Expert
18/03/2018 - 02:25
Countifs can help you with the problem.
User
18/03/2018 - 02:25
so the formula should tell me how many times a name appears between the specified dates
Excelchat Expert
18/03/2018 - 02:25
Let me show you how.
User
18/03/2018 - 02:26
ok cool
Excelchat Expert
18/03/2018 - 02:26
Yup.
Excelchat Expert
18/03/2018 - 02:28
The formula here =COUNTIFS(A:A,D1,C:C,"<="&G2,C:C,">="&G1)
User
18/03/2018 - 02:28
ok i see
User
18/03/2018 - 02:28
and it seems to do what i want
Excelchat Expert
18/03/2018 - 02:28
Cool.
User
18/03/2018 - 02:28
when i read it it seems to count D1 between the dates
Excelchat Expert
18/03/2018 - 02:29
So, would you input dates in other cells of column G too?
User
18/03/2018 - 02:29
is that correct or am i reading it wrong?
User
18/03/2018 - 02:29
no
Excelchat Expert
18/03/2018 - 02:29
Yes, that is correct. Counting D1.
User
18/03/2018 - 02:29
i would only specify the dates period in G1:G2
Excelchat Expert
18/03/2018 - 02:30
=COUNTIFS(A:A,D1,C:C,"<="&$G$2,C:C,">="&$G$1)
Excelchat Expert
18/03/2018 - 02:30
the formula corrected accordingly to G1:G2
User
18/03/2018 - 02:30
but in my real sheet it may be a different place however i can adjust
User
18/03/2018 - 02:30
ah ha i c
Excelchat Expert
18/03/2018 - 02:30
Ah ha. Okay so you wanna know the problem with the formula in column E?
User
18/03/2018 - 02:31
no there is no problem
Excelchat Expert
18/03/2018 - 02:31
If you look now, you will see tom is there in column a 3 times.
User
18/03/2018 - 02:31
it counts the number of times the name appears in the entire A:A
Excelchat Expert
18/03/2018 - 02:31
But the formula still counting 2.
User
18/03/2018 - 02:31
this was correct but i also wanted it to count between dates
Excelchat Expert
18/03/2018 - 02:32
HMM
Excelchat Expert
18/03/2018 - 02:32
Do you understand now what I'm saying?
User
18/03/2018 - 02:32
H1 is correct isn't it?
Excelchat Expert
18/03/2018 - 02:33
Yup, H1 is correct.
User
18/03/2018 - 02:33
ok then ill quickly try it in my real file i just have to type by hand as the real file is on a work computer accessed by another laptop connected remotley
User
18/03/2018 - 02:34
it might take a minute or two
Excelchat Expert
18/03/2018 - 02:34
okay. .
User
18/03/2018 - 02:39
one second nearly finished i think it works
User
18/03/2018 - 02:40
I'm just checking to make sure
Excelchat Expert
18/03/2018 - 02:40
Not a problem.
Excelchat Expert
18/03/2018 - 02:40
Take your time.
Excelchat Expert
18/03/2018 - 02:48
Only 13 minutes remaining. We can't expand again after that.
User
18/03/2018 - 02:49
it worked but then i accidentally lost the formula and i have to retype and its a long formula to type damm
User
18/03/2018 - 02:49
hang on
Excelchat Expert
18/03/2018 - 02:49
I'm here.
Excelchat Expert
18/03/2018 - 02:56
A reminder, only 5 minutes remaining now.
User
18/03/2018 - 02:57
ok so i think it works i had to type the whole formula twice and then made a typo that i couldn't see so i had to read through the formula on this site and check on the file on my other laptop...Geeze it was hard
Excelchat Expert
18/03/2018 - 02:57
Thanks for trying out excel got it pro.
Excelchat Expert
18/03/2018 - 02:58
have a great day ahead.
User
18/03/2018 - 02:58
thanks i will go to bed soon its 2 am here in OZ
Excelchat Expert
18/03/2018 - 02:59
Have a good nights sleep.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
