Formula Library Test Me Challenges Sandbox

COUNTIFS

Math & Aggregationintermediate

COUNTIFS is to COUNTIF what SUMIFS is to SUMIF — it lets you count rows matching two or more conditions at once. Same multi-criteria pattern, just counting instead of summing.

Syntax

Excel:=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Google Sheets: Same syntax

Parameters

criteria_range1The first range to evaluate.

criteria1The condition for the first range.

criteria_range2, criteria2?Additional range/condition pairs.

? = optional parameter

Worked Example

How many Overdue invoices are from the Engineering department?

Row numbers	F	G
Header row	department	status
2	Engineering	Overdue
3	Finance	Paid
4	Engineering	Paid
5	Engineering	Overdue
6	Marketing	Overdue
7	Engineering	Pending

Formula

=COUNTIFS(B2:B7, "Overdue", A2:A7, "Engineering")

Result

COUNTIFS checks both columns row by row across A2:B7. Only rows 2 and 5 are both 'Engineering' and 'Overdue', so the count is 2.

Related:COUNTIF SUMIFS AVERAGEIFS

Practice Exercises

2 exercises - work through each one using the data table below

Exercise 1 of 2intermediate

#COUNTIFS-001

Question

Sarah Chen is an approver and you want to audit her Software approvals. How many invoices have a category of 'Software' AND were approved by Sarah Chen?

Dataset

Vendor Spend Tracker (150 rows total)

Download CSV

Row numbers	A	B	C	D	E	F
Header row
2	INV-1001	Cloudline Software	Software	$12,500.00	Paid	Sarah Chen
3	INV-1002	Apex Office Supply	Office Supplies	$487.50	Paid	Marcus Rivera
4	INV-1003	Summit Consulting Group	Consulting	$18,750.00	Paid	Priya Patel
5	INV-1004	Meridian Travel Co.	Travel	$3,200.00	Paid	Laura Thompson
6	INV-1005	Forge & Frame Creative	Marketing	$7,600.00	Paid	James Okafor
7	INV-1006	NovaTech Solutions	Software	$9,800.00	Pending	Sarah Chen
8	INV-1007	Atlas Hardware Co.	Hardware	$2,150.00	Paid	Marcus Rivera
9	INV-1008	Clearview Data Systems	Software	$15,300.00	Overdue	David Kim
10	INV-1009	Redstone Marketing	Marketing	$4,250.00	Paid	James Okafor
11	INV-1010	Keystone Legal Advisors	Consulting	$8,900.00	Paid	David Kim
12	INV-1011	Cloudline Software	Software	$8,500.00	Paid	Sarah Chen
13	INV-1012	Basecamp Events	Marketing	$8,256.80	Paid	Laura Thompson
14	INV-1013	Apex Office Supply	Office Supplies	$713.88	Paid	Marcus Rivera
15	INV-1014	BrightPath Analytics	Software	$2,645.33	Paid	Laura Thompson
16	INV-1015	Meridian Travel Co.	Travel	$4,702.01	Paid	Priya Patel
17	INV-1016	Clearview Data Systems	Software	$3,400.74	Paid	James Okafor
18	INV-1017	Apex Office Supply	Office Supplies	$531.83	Paid	Marcus Rivera
19	INV-1018	Summit Consulting Group	Consulting	$1,847.86	Paid	Priya Patel
20	INV-1019	Cloudline Software	Software	$7,800.00	Paid	Sarah Chen
21	INV-1020	Atlas Hardware Co.	Hardware	$2,524.82	Paid	Marcus Rivera
22	INV-1021	Meridian Travel Co.	Travel	$2,934.55	Paid	Priya Patel
23	INV-1022	Atlas Hardware Co.	Hardware	$2,912.75	Pending	Sarah Chen
24	INV-1023	Atlas Hardware Co.	Hardware	$4,124.04	Paid	Sarah Chen
25	INV-1024	Apex Office Supply	Office Supplies	$672.77	Paid	Marcus Rivera
26	INV-1025	Redstone Marketing	Marketing	$5,371.63	Paid	Sarah Chen
27	INV-1026	Clearview Data Systems	Software	$3,796.96	Paid	Sarah Chen
28	INV-1027	Horizon IT Services	Software	$1,773.30	Paid	Marcus Rivera
29	INV-1028	Basecamp Events	Marketing	$4,601.68	Paid	Sarah Chen
30	INV-1029	Redstone Marketing	Marketing	$5,414.85	Paid	James Okafor
31	INV-1030	BrightPath Analytics	Software	$2,704.87	Paid	Sarah Chen
32	INV-1031	Basecamp Events	Marketing	$2,351.63	Paid	Priya Patel
33	INV-1032	Cloudline Software	Software	$9,200.00	Paid	Sarah Chen
34	INV-1033	Clearview Data Systems	Software	$4,086.27	Pending	Marcus Rivera
35	INV-1034	Meridian Travel Co.	Travel	$1,642.50	Paid	Laura Thompson
36	INV-1035	Cloudline Software	Software	$2,867.94	Paid	Sarah Chen
37	INV-1036	Horizon IT Services	Software	$4,586.87	Paid	Laura Thompson
38	INV-1037	BrightPath Analytics	Software	$2,679.33	Paid	Sarah Chen
39	INV-1038	Cloudline Software	Software	$2,284.90	Paid	James Okafor
40	INV-1039	Horizon IT Services	Software	$3,224.17	Paid	James Okafor
41	INV-1040	Apex Office Supply	Office Supplies	$604.96	Paid	Sarah Chen
42	INV-1041	Summit Consulting Group	Consulting	$9,500.00	Overdue	Priya Patel
43	INV-1042	Clearview Data Systems	Software	$3,871.90	Paid	Priya Patel
44	INV-1043	Forge & Frame Creative	Marketing	$5,726.55	Paid	James Okafor
45	INV-1044	NovaTech Solutions	Software	$3,596.33	Paid	James Okafor
46	INV-1045	Meridian Travel Co.	Travel	$5,657.73	Paid	Laura Thompson
47	INV-1046	Cloudline Software	Software	$8,000.00	Paid	Sarah Chen
48	INV-1047	Apex Office Supply	Office Supplies	$396.69	Paid	Laura Thompson
49	INV-1048	Apex Office Supply	Office Supplies	$523.99	Paid	Marcus Rivera
50	INV-1049	Pinecrest Staffing	Consulting	$5,843.41	Paid	Priya Patel
51	INV-1050	Clearview Data Systems	Software	$5,091.38	Paid	Sarah Chen
52	INV-1051	BrightPath Analytics	Software	$6,200.00	Overdue	Marcus Rivera
53	INV-1052	Keystone Legal Advisors	Consulting	$10,496.53	Paid	David Kim
54	INV-1053	Cloudline Software	Software	$3,873.77	Paid	Sarah Chen
55	INV-1054	NovaTech Solutions	Software	$2,131.07	Paid	Sarah Chen
56	INV-1055	Basecamp Events	Marketing	$4,659.43	Paid	Sarah Chen
57	INV-1056	Cloudline Software	Software	$7,500.00	Paid	Sarah Chen
58	INV-1057	NovaTech Solutions	Software	$3,255.52	Paid	Sarah Chen
59	INV-1058	Clearview Data Systems	Software	$2,889.22	Paid	Priya Patel
60	INV-1059	Basecamp Events	Marketing	$2,943.88	Paid	Marcus Rivera
61	INV-1060	Horizon IT Services	Software	$3,917.25	Paid	James Okafor
62	INV-1061	Atlas Hardware Co.	Hardware	$2,958.71	Paid	Marcus Rivera
63	INV-1062	Forge & Frame Creative	Marketing	$5,190.48	Paid	James Okafor
64	INV-1063	Meridian Travel Co.	Travel	$3,800.00	Overdue	Marcus Rivera
65	INV-1064	Meridian Travel Co.	Travel	$3,248.96	Pending	Sarah Chen
66	INV-1065	Cloudline Software	Software	$6,800.00	Paid	Sarah Chen
67	INV-1066	Redstone Marketing	Marketing	$4,583.65	Paid	Sarah Chen
68	INV-1067	Horizon IT Services	Software	$4,154.73	Paid	James Okafor
69	INV-1068	Cloudline Software	Software	$1,994.85	Paid	Sarah Chen
70	INV-1069	NovaTech Solutions	Software	$5,988.79	Paid	Marcus Rivera
71	INV-1070	Apex Office Supply	Office Supplies	$701.90	Paid	Sarah Chen
72	INV-1071	Redstone Marketing	Marketing	$1,522.67	Paid	Sarah Chen
73	INV-1072	NovaTech Solutions	Software	$8,100.00	Overdue	Priya Patel
74	INV-1073	Summit Consulting Group	Consulting	$33,000.00	Paid	Priya Patel
75	INV-1074	Atlas Hardware Co.	Hardware	$3,618.53	Paid	Sarah Chen
76	INV-1075	BrightPath Analytics	Software	$5,484.82	Paid	James Okafor
77	INV-1076	Clearview Data Systems	Software	$2,190.73	Paid	Sarah Chen
78	INV-1077	Meridian Travel Co.	Travel	$4,915.98	Paid	Marcus Rivera
79	INV-1078	Pinecrest Staffing	Consulting	$5,337.06	Paid	Marcus Rivera
80	INV-1079	Meridian Travel Co.	Travel	$5,217.40	Paid	Priya Patel
81	INV-1080	Greenfield Facilities	Office Supplies	$4,199.01	Paid	Sarah Chen
82	INV-1081	Keystone Legal Advisors	Consulting	$7,880.94	Paid	David Kim
83	INV-1082	Apex Office Supply	Office Supplies	$738.04	Paid	Laura Thompson
84	INV-1083	Cloudline Software	Software	$9,500.00	Paid	Sarah Chen
85	INV-1084	Meridian Travel Co.	Travel	$3,706.27	Paid	Priya Patel
86	INV-1085	Horizon IT Services	Software	$3,934.09	Paid	Priya Patel
87	INV-1086	Cloudline Software	Software	$2,463.38	Paid	Laura Thompson
88	INV-1087	Apex Office Supply	Office Supplies	$404.67	Paid	Marcus Rivera
89	INV-1088	Pinecrest Staffing	Consulting	$1,909.04	Paid	James Okafor
90	INV-1089	NovaTech Solutions	Software	$2,885.01	Paid	Priya Patel
91	INV-1090	Meridian Travel Co.	Travel	$5,922.08	Paid	Marcus Rivera
92	INV-1091	Redstone Marketing	Marketing	$3,693.48	Paid	Laura Thompson
93	INV-1092	Cloudline Software	Software	$4,986.06	Paid	Laura Thompson
94	INV-1093	Cloudline Software	Software	$31,500.00	Paid	Sarah Chen
95	INV-1094	BrightPath Analytics	Software	$4,248.05	Paid	James Okafor
96	INV-1095	Redstone Marketing	Marketing	$3,264.04	Paid	James Okafor
97	INV-1096	BrightPath Analytics	Software	$5,977.55	Paid	Marcus Rivera
98	INV-1097	Redstone Marketing	Marketing	$3,214.26	Paid	Marcus Rivera
99	INV-1098	Cloudline Software	Software	$8,200.00	Paid	Sarah Chen
100	INV-1099	Meridian Travel Co.	Travel	$2,709.54	Paid	Priya Patel
101	INV-1100	Meridian Travel Co.	Travel	$3,516.17	Pending	Sarah Chen
102	INV-1101	Forge & Frame Creative	Marketing	$3,122.47	Paid	James Okafor
103	INV-1102	Atlas Hardware Co.	Hardware	$4,530.21	Paid	Laura Thompson
104	INV-1103	NovaTech Solutions	Software	$4,352.17	Paid	Laura Thompson
105	INV-1104	Meridian Travel Co.	Travel	$3,137.30	Paid	Priya Patel
106	INV-1105	NovaTech Solutions	Software	$4,559.89	Paid	Laura Thompson
107	INV-1106	Pinecrest Staffing	Consulting	$7,400.00	Overdue	Marcus Rivera
108	INV-1107	Basecamp Events	Marketing	$2,724.75	Pending	James Okafor
109	INV-1108	Redstone Marketing	Marketing	$3,957.64	Paid	Marcus Rivera
110	INV-1109	NovaTech Solutions	Software	$2,398.15	Paid	Laura Thompson
111	INV-1110	Horizon IT Services	Software	$5,015.14	Paid	Laura Thompson
112	INV-1111	Pinecrest Staffing	Consulting	$30,500.00	Paid	Laura Thompson
113	INV-1112	NovaTech Solutions	Software	$3,646.25	Pending	Marcus Rivera
114	INV-1113	Cloudline Software	Software	$5,200.86	Pending	Laura Thompson
115	INV-1114	Meridian Travel Co.	Travel	$1,878.57	Paid	Sarah Chen
116	INV-1115	Clearview Data Systems	Software	$4,287.78	Paid	Priya Patel
117	INV-1116	Cloudline Software	Software	$5,505.30	Paid	Priya Patel
118	INV-1117	Clearview Data Systems	Software	$4,658.66	Paid	Marcus Rivera
119	INV-1118	Greenfield Facilities	Office Supplies	$5,042.80	Paid	Laura Thompson
120	INV-1119	Cloudline Software	Software	$7,000.00	Paid	Sarah Chen
121	INV-1120	Horizon IT Services	Software	$3,893.42	Paid	Marcus Rivera
122	INV-1121	Meridian Travel Co.	Travel	$5,864.21	Paid	Priya Patel
123	INV-1122	Redstone Marketing	Marketing	$3,920.27	Paid	Priya Patel
124	INV-1123	Redstone Marketing	Marketing	$4,600.00	Overdue	Sarah Chen
125	INV-1124	NovaTech Solutions	Software	$1,602.74	Paid	Marcus Rivera
126	INV-1125	Cloudline Software	Software	$5,250.09	Paid	Sarah Chen
127	INV-1126	Meridian Travel Co.	Travel	$4,417.14	Paid	Sarah Chen
128	INV-1127	Horizon IT Services	Software	$5,900.00	Overdue	Priya Patel
129	INV-1128	Forge & Frame Creative	Marketing	$4,618.54	Paid	James Okafor
130	INV-1129	Cloudline Software	Software	$8,800.00	Paid	Sarah Chen
131	INV-1130	BrightPath Analytics	Software	$4,003.25	Paid	Marcus Rivera
132	INV-1131	Keystone Legal Advisors	Consulting	$11,892.88	Paid	David Kim
133	INV-1132	BrightPath Analytics	Software	$2,800.34	Paid	James Okafor
134	INV-1133	Meridian Travel Co.	Travel	$4,365.68	Paid	Marcus Rivera
135	INV-1134	NovaTech Solutions	Software	$2,264.09	Paid	Marcus Rivera
136	INV-1135	Cloudline Software	Software	$5,344.04	Paid	James Okafor
137	INV-1136	Greenfield Facilities	Office Supplies	$1,733.29	Paid	Marcus Rivera
138	INV-1137	Meridian Travel Co.	Travel	$2,251.07	Paid	James Okafor
139	INV-1138	Apex Office Supply	Office Supplies	$367.30	Paid	Marcus Rivera
140	INV-1139	Pinecrest Staffing	Consulting	$3,235.55	Paid	Sarah Chen
141	INV-1140	Pinecrest Staffing	Consulting	$4,538.25	Paid	Laura Thompson
142	INV-1141	Cloudline Software	Software	$7,200.00	Paid	Sarah Chen
143	INV-1142	NovaTech Solutions	Software	$2,487.53	Paid	Laura Thompson
144	INV-1143	Greenfield Facilities	Office Supplies	$4,398.73	Paid	Sarah Chen
145	INV-1144	Clearview Data Systems	Software	$4,282.05	Paid	Sarah Chen
146	INV-1145	Apex Office Supply	Office Supplies	$300.36	Paid	Laura Thompson
147	INV-1146	Cloudline Software	Software	$9,000.00	Paid	Sarah Chen
148	INV-1147	Summit Consulting Group	Consulting	$4,697.90	Paid	Sarah Chen
149	INV-1148	NovaTech Solutions	Software	$2,613.37	Paid	James Okafor
150	INV-1149	Greenfield Facilities	Office Supplies	$4,097.38	Paid	Sarah Chen
151	INV-1150	Greenfield Facilities	Office Supplies	$1,846.11	Pending	Priya Patel
152

Click a column name to sort. Use the filter icon to narrow rows by column value.

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Exercise 2 of 2intermediate

#COUNTIFS-002

Question

How many invoices are both over $5,000 in amount AND currently in 'Pending' status? These are the high-value items that need urgent follow-up.