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
FYI you'd be better off not hardcoding the date string in the criteria - use a DATE function - eg:

">="&DATE(2020,12,31)

which cannot be misconstrued.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RichardX720

New Member
Joined
Feb 3, 2021
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
My GOD your a rockstar... I was just looking for a way to do that :)
 

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
Better yet, put the date in a cell and refer to that. You then format it to just display the month name for example.
 

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