HELP with COUNTIFS :( I have a single hair left, please save it

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
I have an excel document that I created on OSX Excel 2016. I no longer have a Mac and now using Windows Excel 2019. I opened the sheet and added more content for it to calculate and everything went to 0's...

I cannot figure it out why.. I only pasted a few lines of VData below but you get the example.. Its USED to count how many times "UserName 1" showed up in Column C of VData inbetween the dates mentioned checked against Colum D of VData. The cell dates are dd/mm/yyyy and I am using US Region but I have tried to change region to NL and still doesnt work... PLEASE HELP!!

2019-2020_USERS_TESTING.xls
ABCDEFGHIJKLMN
1Doctor2020JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2UserName 10000000000000
3UserName 20000000000000
4UserName 30000000000000
5UserName 40000000000000
6UserName 50000000000000
7UserName 60000000000000
8UserName 70000000000000
9UserName 80000000000000
10UserName 90000000000000
11UserName 100000000000000
12UserName 110000000000000
13UserName 120000000000000
14UserName 130000000000000
15UserName 140000000000000
16UserName 150000000000000
17UserName 160000000000000
18UserName 170000000000000
19Totals0000000000000
CONSULTS
Cell Formulas
RangeFormula
B2:B18B2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/12/2020")
C2:C18C2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/01/2020")
D2:D18D2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/02/2020",VData!D:D,"<="&"28/02/2020")
E2:E18E2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/03/2020",VData!D:D,"<="&"31/03/2020")
F2:F18F2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/04/2020",VData!D:D,"<="&"30/04/2020")
G2:G18G2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/05/2020",VData!D:D,"<="&"31/05/2020")
H2:H18H2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/06/2020",VData!D:D,"<="&"30/06/2020")
I2:I18I2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/07/2020",VData!D:D,"<="&"31/07/2020")
J2:J18J2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/08/2020",VData!D:D,"<="&"31/08/2020")
K2:K18K2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/09/2020",VData!D:D,"<="&"30/09/2020")
L2:L18L2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/10/2020",VData!D:D,"<="&"31/10/2020")
M2:M18M2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/11/2020",VData!D:D,"<="&"30/11/2020")
N2:N18N2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/12/2020",VData!D:D,"<="&"31/12/2020")
B19B19=SUM([2020])
C19C19=SUM([JAN])
D19D19=SUM([FEB])
E19E19=SUM([MAR])
F19F19=SUM([APR])
G19G19=SUM([MAY])
H19H19=SUM([JUN])
I19I19=SUM([JUL])
J19J19=SUM([AUG])
K19K19=SUM([SEP])
L19L19=SUM([OCT])
M19M19=SUM([NOV])
N19N19=SUM([DEC])




2019-2020_USERS_TESTING.xls
ABCDE
1324ConsultUserName 102/01/2019
1424ConsultUserName 1202/01/2019
15761ConsultUsername 202/01/2019
163018ConsultUsername 1402/01/2019
173018ConsultUsername 302/01/2019
183018ConsultUsername 902/01/2019
VData
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
In that case your dates are text, when a real date is formatted as General you would see a 5 figure number
As date
+Fluff 1.xlsm
B
201/02/2021
302/02/2021
403/02/2021
504/02/2021
605/02/2021
706/02/2021
807/02/2021
Results


as General
+Fluff 1.xlsm
B
244228
344229
444230
544231
644232
744233
844234
Results
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
How it looks now if i select the cell..
View attachment 31225

How it looks when I change it to GENERAL
View attachment 31226
SORRY.. correction on this.. It is set to CUSTOM because being on US region I have the date set to dd/mm/yyyy
date_cell_custom.png



BUT something odd I do notice.. If I switch to General on any date that is mm/dd/yy it changes to for example 01/01/2019 to 43466 but any date that IS in the EU format 23/12/2019 does not do that...??? I am so confused...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
Any date that does not change to a number is text & not a date.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Those are text, not true dates then. Select the column, do Data - Text to Columns, choose delimited, then leave all the options blank in the next step, then in the last step specify that its a date field in DMY order. That should convert them to true dates.
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Ok.. that makes sense.. what I cant seem to do is this then: I have the cell set to CUSTOM dd/mm/yyyy and I type in 22/06/2020 but it stays as text and not as a date...
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Those are text, not true dates then. Select the column, do Data - Text to Columns, choose delimited, then leave all the options blank in the next step, then in the last step specify that its a date field in DMY order. That should convert them to true dates.
I did what you said but it still doesnt seem to work on any dates in the EU format..

date_format.png
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I did what you said but it still doesnt seem to work on any dates in the EU format..

View attachment 31228
NEVERMIND.... didnt select the right date format :)


Ok so I got them all to show up now as dates.. COUNTIFS still not working :(
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Can you post a workbook? I've never seen that fail before.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are the dates correct? Are all your COUNTIFS formulas still 0?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top