Count IFs not counting data correctly

[dawidmns]

Hi

I have been trying to solve this for 2 days now without the luck.

I need to count "With QC - Trade" within last 30 days, then there will be other within 60 days, 90 days etc, but I need to sort out 30 days first.

I got this formula, data and calculation are on different tabs:

=COUNTIFS('Corporate Master'!F:F,"With QC - TRADE",'Corporate Master'!I:I,"<="&(NOW()-30))

So when I selected the data manualy, I got 11, and the formula counted 9, wham am I doing wrong?

 

[Phuoc]

Try change to:

=COUNTIFS('Corporate Master'!F:F,"With QC - TRADE",'Corporate Master'!I:I,"<="&(TODAY()-30))

 

[jasonb75]

Using <= will give you results older than 30 days, if you want within the last 30 days then you need to use>=

Also, you should note the suggestion from @Phuoc above. Using NOW() in the formula means that it is time of day specific. If there is no time in the cell then dates are evaluated as midnight whilst NOW() looks at time of day as well, meaning that records on the first day of the 30 day window would be excluded.

 

[dawidmns]

Thanks guys, unfortunetely I am still getting 9 instead of 11. I also tried changing <= to >= but none of the results match. I also tried with different cirteria under CDD status and the calculations (not seen on the picture) are not adding up to comparing to data selected manually.

If there anything else I can add here to give you better picture to find out the solution?

 

[Alex Blakenburg]

I agree with @jasonb75 that within the last 30 days means you should be using ">=" in your countifs.
Also since you are using just dates that Today would be better than Now since Now is date and time.
The below works for me.

If you find it doesn't work, as an expirement add a "*" to the end of TRADE. If that fixes it then you have spaces at the end of 2 of the descriptions.
eg

=COUNTIFS('Corporate Master'!F:F,"With QC - TRADE*",'Corporate Master'!I:I,">="&(TODAY()-30))

20220805 Count If Dates dawidmns.xlsx
	F	G	H	I	J	K	L
1
2	CDD Status	CDD Permissions	Current CRR	CDD Expiry Date		Formula
3	With QC - Trade			4/08/2022		11	=COUNTIFS('Corporate Master'!F:F,"With QC - TRADE",'Corporate Master'!I:I,">="&(TODAY()-30))
4	With QC - Trade			4/08/2022
5	With QC - Trade			4/08/2022
6	With QC - Trade			29/07/2022
7	With QC - Trade			31/07/2022
8	With QC - Trade			10/07/2022
9	With QC - Trade			10/07/2022
10	With QC - Trade			10/07/2022
11	With QC - Trade			22/07/2022
12	With QC - Trade			22/07/2022
13	With QC - Trade			3/08/2022
14	With QC - Trade			2/07/2022
15	With QC - Trade			3/07/2022
16	With QC - Trade			30/06/2022
17	With QC - Trade			9/06/2022
18	With QC - Trade			25/06/2022
Corporate Master

Cell Formulas
Range	Formula
K3	K3	=COUNTIFS('Corporate Master'!F:F,"With QC - TRADE",'Corporate Master'!I:I,">="&(TODAY()-30))
L3	L3	=FORMULATEXT(K3)

 

[jasonb75]

One other thing to consider, are the dates correct for your regional settings?

I notice that the dates in the screen capture are UK format, if this is imported data and your system is set for US dates then that is likely to cause errors.

 

[dawidmns]

I believe there is something wrong with the data collected. But I have no idea what is wrong, I have checked it, checked the date etc everything seems fine. Is there anyway I can upload the Excel document here so maybe someone can have a look what might be an issue of wrong calculations?

 

[Joe4]

I believe there is something wrong with the data collected. But I have no idea what is wrong, I have checked it, checked the date etc everything seems fine. Is there anyway I can upload the Excel document here so maybe someone can have a look what might be an issue of wrong calculations?

You can upload the file to any file sharing site, then provide a link for users here, so they can download it.

 

[dawidmns]

Great, please let me know if you can access the file:

Download

Click to download the files for this transfer with TransferNow

www.transfernow.net

 

[Fluff]

That link comes back as "The selected transfer does not exist or has expired."