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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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
I assume you actually have some 2020 dates in the file?
 

RichardX720

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

my mistake for only grabbing a block from 2019 and only showing the table for 2020.. but yet there are 1000's of 2020's
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
SHCF-2019-2020_Doctor-Consults_TESTING.xls
ABCDE
14283426ConsultUserName 101/06/2020
142844461ConsultUsername 1201/06/2020
14285493ConsultUsername 101/06/2020
142864090ConsultUsername 301/06/2020
VData
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,513
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you sure those are dates & not text? If you change the format of the whole column to General, what do you see?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,390
Office Version
  1. 2016
Platform
  1. Windows
Hi RichardX720,
Your data is in US m/d/yyyy format but your search is in dd/mm/yy format.
 

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

ADVERTISEMENT

Any circular reference error messages in the status bar?
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi RichardX720,
Your data is in US m/d/yyyy format but your search is in dd/mm/yy format.

no its actually not, I just snapped shots of the lower months so its hard to tell.. :)
 

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Are you sure those are dates & not text? If you change the format of the whole column to General, what do you see?
How it looks now if i select the cell..
date_cell.png


How it looks when I change it to GENERAL
date_cell_general.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,183
Members
416,077
Latest member
SJSB

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