I need help creating a formula that counts entries based on several conditions at once. My spreadsheet has these columns:
Submission Time
Manager Name
Event Date
Department
Project Group
Request Category
Request Details
I want to count how many entries match specific criteria. For example, I need to count all Request Categories where the Manager Name equals a certain value, Department matches what I specify, and the Event Date falls within a range I set.
Basically I’m trying to do something like this:
COUNT [Request Category] WHERE ([Manager Name = 'John'] AND [Department = 'Sales'] AND [Event Date >= '2024-01-01'] AND [Event Date <= '2024-12-31'])
I want to put the manager name, department, and date values in separate cells so I can change them easily. Then the count result should update automatically. This way I can make charts from the results.
Can anyone show me the right COUNTIFS syntax for this? I tried a few approaches but keep getting errors or wrong numbers.
Double check your date formatting - Google Sheets gets picky about this stuff. If you’re getting weird results, try wrapping your date references like =COUNTIFS(B:B,E2,D:D,F2,C:C,">="&DATE(YEAR(G2),MONTH(G2),DAY(G2))) instead of just referencing the cell directly. Saved me tons of headaches when dates weren’t getting recognized properly.
Your syntax looks fine, but make sure you’re counting the right column. You said Request Category in your example, but COUNTIFS counts rows, not column values. If you want rows where Request Category has a value, add that to your criteria: =COUNTIFS(B:B,"John",D:D,"Sales",C:C,">="&DATE(2024,1,1),C:C,"<="&DATE(2024,12,31),F:F,"<>") - that last part makes sure Request Category isn’t blank. I made the same mistake thinking COUNTIFS would skip empty cells in my target column. Also check for hidden spaces in your data - TRIM saved me when my counts were off.
COUNTIFS gets tricky with date ranges, but here’s what works. You can’t use two operators in one condition, so you need separate conditions for the date range. Try this: =COUNTIFS(B:B,E2,D:D,F2,C:C,">="&G2,C:C,"<="&H2) where E2 is your manager name, F2 is department, G2 is start date, and H2 is end date. The trick is splitting the date range - one condition for >= start date, another for <= end date. Make sure your dates are actual date values, not text. I wasted hours debugging once because my reference cells were text instead of dates. Also check your column references match your layout since you mentioned Request Category but didn’t say which column to count.