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
 

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
One quick question - in the Advanced section of Excel options, at the bottom, do you have any of the Lotus compatibility options checked?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
That works for me
CONSULT_TESTING.xlsx
ABCDEFGHIJKLMN
1Doctor2020JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2UserName 19301230000000
3UserName 21010000000000
4UserName 31001000000000
5UserName 42000200000000
6UserName 51000010000000
7UserName 62000020000000
8UserName 72000020000000
9UserName 80000000000000
10UserName 90000000000000
11UserName 100000000000000
12Totals18312480000000
CONSULTS
Cell Formulas
RangeFormula
B2:B11B2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/12/2020")
C2:C11C2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/01/2020")
D2:D11D2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/02/2020",VData!D:D,"<="&"28/02/2020")
E2:E11E2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/03/2020",VData!D:D,"<="&"31/03/2020")
F2:F11F2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/04/2020",VData!D:D,"<="&"30/04/2020")
G2:G11G2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/05/2020",VData!D:D,"<="&"31/05/2020")
H2:H11H2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/06/2020",VData!D:D,"<="&"30/06/2020")
I2:I11I2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/07/2020",VData!D:D,"<="&"31/07/2020")
J2:J11J2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/08/2020",VData!D:D,"<="&"31/08/2020")
K2:K11K2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/09/2020",VData!D:D,"<="&"30/09/2020")
L2:L11L2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/10/2020",VData!D:D,"<="&"31/10/2020")
M2:M11M2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/11/2020",VData!D:D,"<="&"30/11/2020")
N2:N11N2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/12/2020",VData!D:D,"<="&"31/12/2020")
B12:N12B12=SUM(B2:B11)


Do you have calculation set to automatic?
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
That works for me
CONSULT_TESTING.xlsx
ABCDEFGHIJKLMN
1Doctor2020JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2UserName 19301230000000
3UserName 21010000000000
4UserName 31001000000000
5UserName 42000200000000
6UserName 51000010000000
7UserName 62000020000000
8UserName 72000020000000
9UserName 80000000000000
10UserName 90000000000000
11UserName 100000000000000
12Totals18312480000000
CONSULTS
Cell Formulas
RangeFormula
B2:B11B2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/12/2020")
C2:C11C2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/01/2020",VData!D:D,"<="&"31/01/2020")
D2:D11D2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/02/2020",VData!D:D,"<="&"28/02/2020")
E2:E11E2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/03/2020",VData!D:D,"<="&"31/03/2020")
F2:F11F2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/04/2020",VData!D:D,"<="&"30/04/2020")
G2:G11G2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/05/2020",VData!D:D,"<="&"31/05/2020")
H2:H11H2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/06/2020",VData!D:D,"<="&"30/06/2020")
I2:I11I2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/07/2020",VData!D:D,"<="&"31/07/2020")
J2:J11J2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/08/2020",VData!D:D,"<="&"31/08/2020")
K2:K11K2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/09/2020",VData!D:D,"<="&"30/09/2020")
L2:L11L2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/10/2020",VData!D:D,"<="&"31/10/2020")
M2:M11M2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/11/2020",VData!D:D,"<="&"30/11/2020")
N2:N11N2=COUNTIFS(VData!C:C,CONSULTS!A2,VData!D:D,">="&"01/12/2020",VData!D:D,"<="&"31/12/2020")
B12:N12B12=SUM(B2:B11)


Do you have calculation set to automatic?
THERE GOES MY LAST HAIR!
 

RichardX720

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

ADVERTISEMENT

Ok so why does it NOT work on my system? Is you PC region set to US standards or EU?

Do I have to press something to make it calculate?
 

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
If you press f9 does it work?
 

RichardX720

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

ADVERTISEMENT

No but if I change my PC REGION to Netherlands.. It now works :( ughhhhhhh.. Ok now the question is none of our finance computers are set in NL region.. SOO it there any way to get this to calculate correctly on workstations set to US region?
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
No but if I change my PC REGION to Netherlands.. It now works :( ughhhhhhh.. Ok now the question is none of our finance computers are set in NL region.. SOO it there any way to get this to calculate correctly on workstations set to US region?
I got it... So my COUNTIFS are asking for 31/12/2020 which of course THAT is not correct when the region is USA.. The cells are set to dd/mm/yyyy so as soon as I switch the COUNTIFS to US standards it calculates the cells correctly because it KNOWS the cell itself is dd/mm/yyyy,,, I hate myself
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Thank you all for the help through this :) I can have a lunch break now.. and a coffee.. and a cookie....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 

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