Countif not counting???

[deletedalien]

ok i have a SIMPLE simple question......

I have the following file and it contains the following formula:

=COUNTIFS(E:E,A3,D:D,">="&$A$1,D:D,"<="&$B$1)

it basically counts the number of instances of user1 within a range of dates on cells A1 and B1

however it doesn't work for user1 (should be 23)
but yet has NO problem with user2 it IS 29 so..... WHAT GIVES!???

i uploaded the file to my onedrive here:

https://1drv.ms/x/s!Agyr_mEIeS75mAVOc_Cq_PTKoTcu

if ANYONE has any input i would greatly appreciate it.

Cheers.

 

[mrhstn]

It should give 21 for User1, and that is what it gives...

 

[AhoyNC]

The issue is your column D has both a date and a time in it (it's just formatted to show only the date), see below.
Your cell has a date of 6/12/17 with no time so Excel sees this as 6/12/17 12:00 AM, so your last two dates are not less than 6/12/17 12:00 AM,

Excel Workbook
	B	C	D	E	F
1	6/12/2017		2-Jun-17	User1
2			2-Jun-17	User1
3			2-Jun-17	User1
4			2-Jun-17	User1
5			6/5/17 8:10 AM	User1
6			6/5/17 9:25 AM	User1
7			6/5/17 9:36 AM	User1
8			6/5/17 10:04 AM	User1
9			6/5/17 10:15 AM	User1
10			6/5/17 11:24 AM	User1
11			6/5/17 12:23 PM	User1
12			6/5/17 1:11 PM	User1
13			6/5/17 1:15 PM	User1
14			6/6/17 6:36 AM	User1
15			6/6/17 7:33 AM	User1
16			6/6/17 10:49 AM	User1
17			6/6/17 11:09 AM	User1
18			6/6/17 11:37 AM	User1
19			6/6/17 12:56 PM	User1
20			6/6/17 1:27 PM	User1
21			6/7/17 2:10 PM	User1
22			6/8/17 8:18 AM	User1
23			6/8/17 1:12 PM	User1
24			6/9/17 9:39 AM	User1
25			6/9/17 11:05 AM	User1
26			6/12/17 9:58 AM	User1	FALSE
27			6/12/17 11:53 AM	User1	FALSE
Sheet

 

[Rick Rothstein]

ok i have a SIMPLE simple question......

I have the following file and it contains the following formula:

=COUNTIFS(E:E,A3,D:D,">="&$A$1,D:D,"<="&$B$1)

it basically counts the number of instances of user1 within a range of dates on cells A1 and B1

however it doesn't work for user1 (should be 23)

The problem is your dates have time values associated with them... in particular, the two June 12th dates. Your formula is checking for dates before or equal to 6/12/2017. Because you did not put a time value with that date, its time value defaults 0:00; however, your two June 12th dates in the table have time values greater than 0:00 so they are outside of the range you are testing for. Change your search date to 6/13/2017 and change the last test to a simple less than instead of the less than or equal to and your formula will work the way you want.

but yet has NO problem with user2 it IS 29 so..... WHAT GIVES!???

No, your posted formula does not work correctly for User2 because you missed counting the three 6/10/2017 dates that are located after 6/12/2017 in the table. Actually, let me rephrase that... the formula counted the three 6/10/2017 dates after the three 6/12/2017 dates... it did not count the 6/12/2017 dates for the reason stated above... it is you who stopped counting at 6/12/2017 and did not count the three 6/10/2017 dates after them. The fact there were three balanced out the improper count that you did.



EDIT NOTE: I see AhoyNC posted my first point before I did (I guess I was writing my second point up and he "snuck" in before I finished writing everything up). In any case.... kudos to AhoyNC for getting there first.

 

[deletedalien]

ohhh man!!!!

Initially i was like "what are these guys talking about, i clearly said equal or less than" and 2 seconds latter i was like ooooooohhhhhh.... DUHHH!!!!!

Because what I see is 6/12/2017, boom that's it done!

BUT..

i didn't think of how Excel sees it....

42,898.48 INSTEAD of 42,898.00 and of course those decimals (.48) makes the number GREATER than 42,898.00

so both you guys are absolutely right!

THANK you so much i was so close to banging my head on my desk over this! hahahaha