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

R

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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?
 
Upvote 0
Fluff said:
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?
Click to expand...
THERE GOES MY LAST HAIR!
 
Upvote 0
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?
 
Upvote 0
If you press f9 does it work?
 
Upvote 0
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?
 
Upvote 0
RichardX720 said:
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?
Click to expand...
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
 
Upvote 0
Thank you all for the help through this :) I can have a lunch break now.. and a coffee.. and a cookie....
 
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
528
Asbestos_Jen
Asbestos_Jen
D
  • Solved
New formula for cumulative values
Replies
6
Views
192
djclements
djclements
O
  • Solved
Counta and Countifs Ignore Formula
Replies
5
Views
87
Peter_SSs
Peter_SSs
A
  • Question Question
Countifs - number of unique values each week
Replies
1
Views
417
adriandwor
A
E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
91
earth
E

Forum statistics

Threads
1,224,766
Messages
6,180,846
Members
453,001
Latest member
coulombevin

Share this page

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
Back
Top