Excluding cells within an CountIF function.

[oosag23]

I have a client tracker where I track the work done throughout the year for each month.
I set up a count if statement to calculate the percentage of work completed for all clients for each month. (X = Complete)
There are monthly and annual clients which I included in the calc.
However I want the calc to exclude the annual clients (Frequency = 1).
I tried working an IF statement into it but couldn’t figure it out.
Any assistance would be much appreciated.

Client Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	List of Business Clients		Marked Completed: X	28%	28%	28%	25%	18%	13%	3%	0%	0%	0%	0%	99%
2
3
4
5								MONTHLY ACCOUNTING UPDATES
6		FREQUENCY		Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec
7	Client 1	1													X
8	Client 2	1													X
9	Client 3	12		X	X	X	X	X	X	X					X
10	Client 4	1													X
11	Client 5	12		X	X	X	X	X	X	X					X
12	Client 6	12		X	X	X	X	X	X						X
13	Client 7	12		X	X	X									X
14	Client 8	12		X	X	X	X	X	X	X					X
15	Client 9	12		X	X	X	X	X							X
16	Client 10	12		X	X	X	X	X	X						X
17	Client 11	12		X	X	X	X	X							X
18	Client 12	12		X	X	X	X	X	X						X
19	Client 13	12		X	X	X	X								X
20	Client 14	12		X	X	X	X	X	X						X
21	Client 15	12		X	X	X	X								X
22	Client 16	12		X	X	X	X	X	X	X					X
23	Client 17	12		X	X	X	X	X	X						X
24	Client 18	1													X
25	Client 19	12		X	X	X	X	X	X						X
26	Client 20	12		X	X	X	X	X	X						X
27	Client 21	12		X	X	X									X
28	Client 22	1													X
29	Client 23	12		X	X	X	X	X	X						X
30	Client 24	12		X	X										X
31	Client 25	12		X	X	X	X	X	X						X
32	Client 26	12		X	X	X	X								X
33	Client 27	12		X	X	X	X	X							X
34	Client 28	12		X	X	X	X	X	X						X
35	Client 29	1													X
36	Client 30	1													X
37	Client 31	12		X	X	X									X
38	Client 32	12		X	X	X	X	X	X	X					X
39	Client 33	12		X	X	X	X	X	X	X					X
40	Client 34	1													X
41	Client 35	12		X	X	X	X	X							X
42	Client 36	12		X	X	X	X	X	X						X
43	Client 37	12		X	X	X	X								X
44	Client 38	1													X
45	Client 39	1													X
46	Client 40	12		X	X	X	X	X	X						X
47	Client 41	12		X	X	X	X								X
48	Client 42	12		X	X	X	X	X	X						X
49	Client 43	1													X
50	Client 44	1													X
51	Client 45	1													X
52	Client 46	1													X
53	Client 47	1													X
54	Client 48	1													X
55	Client 49	12		X	X	X	X	X	X						X
56	Client 50	12		X	X	X	X	X	X						X
57	Client 51	1													X
58	Client 52	12		X	X	X	X	X							X
59	Client 53	1													X
60	Client 54	1		X	X	X	X								X
61	Client 55	12		X	X	X	X								X
62	Client 56	12		X	X	X	X	X	X						X
63	Client 57	12		X	X	X	X	X	X	X					X
64	Client 58	12		X	X	X	X								X
65	Client 59	12		X	X	X	X	X							X
66	Client 60	12		X	X	X	X	X							X
67	Client 61	12		X	X	X									X
68	Client 62	12		X	X	X	X								X
69	Client 63	12		X	X	X	X	X	X						X
70	Client 64	12		X	X	X									X
71	Client 65	12		X	X	X	X	X	X						X
72	Client 66	12		X	X	X	X	X							X
73	Client 67	1													X
74	Client 68	12		X	X	X	X	X	X						X
75	Client 69	12		X	X	X	X	X							X
76	Client 70	12		X	X	X	X	X	X						X
77	Client 71	12		X	X	X									X
78	Client 72	12		X	X	X	X	X	X						X
79	Client 73	12		X	X	X									X
80	Client 74	12		X	X	X	X								X
81	Client 75	1													X
82	Client 76	12		X	X	X	X	X	X						X
83	Client 77	12		X	X	X	X	X	X						X
84	Client 78	1													X
85	Client 79	1		X	X	X									X
86	Client 80	1													X
87	Client 81	1													X
88	Client 82	1		X	X	X	X	X							X
89	Client 83	12		X	X	X	X	X	X						X
90	Client 84	12		X	X	X	X								X
91	Client 85	12		X	X	X	X	X							X
92	Client 86	12		X	X	X	X	X	X	X					X
93	Client 87	12		X	X	X	X	X	X						X
94	Client 88	1													X
95	Client 89	12		X	X	X	X	X	X						X
96	Client 90	1													X
97	Client 91	12		X	X	X	X								X
98	Client 92	12		X	X	X									X
99	Client 93	12		X	X	X	X								X
100	Client 94	1													X
101	Client 95	1													X
102	Client 96	1													X
103	Client 97	1													X
104	Client 98	12		X	X	X	X								X
105	Client 99	12		X	X	X	X	X							X
106	Client 100	1													X
MASTER LIST (3)

Cell Formulas
Range	Formula
D1:O1	D1	=COUNTIF(D$7:D$266,"X")/(COUNTIF(D$7:D$266,"")+COUNTIF(D$7:D$266,"X"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:O1,D4	Cell Value	contains "Prepared"	text	NO
D1:O1,D4	Cell Value	contains "In Process"	text	NO
D1:O1,D4	Cell Value	contains "Not Started"	text	NO
C2:C3	Cell Value	contains "Reviewed"	text	NO
C2:C3	Cell Value	contains "In Process"	text	NO
C2:C3	Cell Value	contains "Not Started"	text	NO
C2:C3	Cell Value	contains "Prepared"	text	NO
C1	Cell Value	contains "Reviewed"	text	NO
C1	Cell Value	contains "In Process"	text	NO
C1	Cell Value	contains "Not Started"	text	NO
C1	Cell Value	contains "Prepared"	text	NO
C4	Cell Value	contains "Reviewed"	text	NO
C4	Cell Value	contains "In Process"	text	NO
C4	Cell Value	contains "Not Started"	text	NO
C4	Cell Value	contains "Prepared"	text	NO
D1:O1,D4	Cell Value	contains "Reviewed"	text	NO
D1:O1,D4	Cell Value	contains "In Process"	text	NO
D1:O1,D4	Cell Value	contains "Not Started"	text	NO
D1:O1,D4	Cell Value	contains "Prepared"	text	NO

 

[RasGhul]

Try

=COUNTIFS(D$7:D$266,"X",B$7:B$266,"<>"&1)/COUNTIFS(D$7:D$266,"")+COUNTIFS(D$7:D$266,"X",B$7:B$266,"<>"&1)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)