CountIF and CountIFS not returning correct result

Welcome to the forum.

You must have some leading or trailing spaces somehere. Using your numbers:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D
32	7
33	7
34	7		34	=COUNTIF(A1:A70,"<=7")
35	8		34	=COUNTIFS(A1:A70,">=1",A1:A70,"<=7")
36	8
37	9

Sheet: Sheet1

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Are you still using Excel 2010???

Please update your profile, which is stuck on Excel 2010, to MS365.

I will have a look.

Here's why:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A
3	0.572083333
4	1.266319444
5	1.315370370
6	1.329155093
7	1.412372685
8	1.468113426
9	1.530740741
10	2.055173611
11	2.057546296
12	2.078969907
13	2.102916667
14	4.316122685
15	4.335706019
16	4.413402778
17	4.441527778
18	4.514236111
19	4.532557870
20	4.581712963
21	4.631759259
22	5.302731481
23	5.306030093
24	5.307592593
25	5.308877315
26	5.335277778
27	5.400879630
28	5.417476852
29	6.314745370
30	6.570428241
31	6.614039352
32	6.617083333
33	7.252939815
34	7.446689815
35	7.633171296
36	8.386481481

Sheet: Sheet1

Try this:

=SUMPRODUCT((ROUND(A1:A70,0)<=7)*(A1:A70<>""))

and this:

=SUMPRODUCT((ROUND(A1:A70,0)<=7)*(ROUND(A1:A70,0)>=1)*(A1:A70<>""))

Rounding via cell formatting does not change the underlying number.

I've attached a piece of the SS where the data is coming from.
Ultimately I want to collate these days into:

Cases - 1 week old
2 weeks old
3 weeks old
etc

Your formulae refer to a workbook on your drive and are returning #REF! errors:

=TODAY()-'\\ms.zurich.com.au\dfs\GroupsGen\LifeDistribution\OnePath Distribution\Adviser Sales\OneCall Team\OneCall Reporting\OneCall reporting - Zurich\2021 - 2022\All open cases new sharepoint.xlsx'!Table_complaints[@Created]+1

Please fix this and post again.

This might be what you want:

=((TODAY()-INT(A2))/7)+1

If post #10 isn't what you want, then we need a workbook with expected results mocked up manually, please. Let me know.

No response ... Looked and then disappeared.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Thanks so much. That was extremely helpful and worked perfectly

You're welcome.