How to do multi-condition counting in Excel: Usage of COUNTIFS function

Key points of this article

COUNTIFS is written in pairs in the order of "area 1, condition 1, area 2, condition 2", and counting is only done when all conditions are met at the same time.
When counting data for this week, you can use two date conditions to limit the range, use ">=" for the lower limit and "<=" for the upper limit, and quote cells instead of hard-coding dates.
The number of rows in the area must be consistent, otherwise the formula will report an error. This is one of the common mistakes in multi-condition counting.
If the date and number are in text format, the conditions may not match, and the counting results will be low. The format needs to be unified first.

Have you counted the number of orders "completed by the sales department" in the table? Single-condition COUNTIF can only look at one column. Once more than two conditions, such as department, status, and date, are to be met at the same time, COUNTIFS is required. This function is commonly used in daily reports, attendance, and customer follow-up forms, but many people get stuck on the parameter order or date writing when they first come into contact with it.

This article is written for readers who already know how to use basic formulas and want to do multi-condition counting correctly. After reading, you can write a counting formula with more than two conditions, use it to count this week's data, and know what points to check when the results are wrong. You can follow the examples in the article directly in your own table, just replace them with your columns and conditions.

To calculate two conditions together, use COUNTIFS or another method? One sentence conclusion

COUNTIFS is a function designed for "counting when multiple conditions are met at the same time". As long as the condition can be written as "a certain column is equal to, greater than, or less than a certain value", it is usually a direct choice. The table below lists common scenarios and recommended practices.

your situation	Recommended practices	reason
There is only one condition, such as only counting "completed"	COUNTIF	The structure is simpler, the formula is shorter, and it is less prone to errors.
Two or more conditions, both of which can be written as comparative expressions	COUNTIFS	Directly list each condition in pairs, and all conditions will take effect at the same time
Count date ranges such as one week and one month	COUNTIFS adds two date conditions	Use ">=" and "<=" to limit the lower limit and upper limit respectively.
The same column must meet both ranges, for example, the score is between 60 and 80	COUNTIFS writes the same range twice	The same column can appear multiple times, each corresponding to a condition
There is an "or" relationship between the conditions, for example, the department is A or B	Count separately and then add them together, or use other functions	There is only an "and" relationship between the conditions of COUNTIFS

Before you start: Confirm these points first

First, confirm that each condition has corresponding data in the same row. COUNTIFS is judged row by row, and each row either meets all conditions at the same time or is not counted. If the "Department" and "Status" of the order are scattered in two tables, the formula will not count them. You need to align the two columns to the same table first.

Second, confirm that the number of rows in the area participating in the counting is consistent. For example, if the range of condition 1 is A2 to A100, and the range of condition 2 is written as B2 to B99, if the number of rows is different, the formula will return an error. Before writing the formula, it is recommended to take a look at the first and last row numbers of the two areas.

Third, confirm that the date is a real date and not text that looks like a date. In data exported from other systems, dates are often left-justified, which is usually text. Text dates cannot participate in ">=" and "<=" comparisons, and the statistical results will be less. You can enter a formula test in a blank cell. If a numerical value is returned instead of the original text, it means it is in date format.

Fourth, clarify the boundaries of statistics. Whether the week starts on Monday or Sunday, and whether it includes weekends, must be decided first. Otherwise, if the formula is written correctly, the results may not match your expectations.

Count the number of orders from "Sales Department and Completed" in Excel: Operation steps

The following takes an order form as an example. Assume column A is department, column B is state, and the data goes from row 2 to row 200.

Open the table and confirm that A2:A200 is the department, B2:B200 is the status, and the number of rows in the two columns is consistent.
Select a blank cell, such as D2, and prepare to enter the formula.
Enter the function name and open the brackets, first write the pair of parameters in the area A2:A200 and the condition "Sales Department". The conditional text must be enclosed in English double quotes.
Then enter the second pair of parameters: area B2:B200, condition "Completed", and then close the brackets. The complete formula is =COUNTIFS(A2:A200, "Sales Department", B2:B200, "Completed").
Press Enter, D2 will display the number of rows that meet both conditions.
Check whether the results are reasonable: You can use the filter function to filter out "Sales Department" first, then look at the number of "completed" rows in the results, and compare it with the formula results.
If the conditions need to change with the table, put "Sales Department" and "Completed" in cells E2 and F2 respectively, and change the formula to reference these two cells, for example =COUNTIFS(A2:A200,E2,B2:B200,F2). In the future, only the cell content can be changed.
When fuzzy matching is required, wildcards can be used in the conditions. For example, "*Department" represents all department names ending with "Department". This step is used as needed.

Statistics for this week: How to write the date range

Counting this week's data is essentially counting the number of rows whose dates fall within a certain range. The method is to add two date conditions to COUNTIFS: use ">=" for the lower limit and "<=" for the upper limit. Suppose the order date is in column C, from C2 to C200.

First put the start and end dates of the interval in the two cells. For example, put this Monday in H2 and this Sunday in H3. Monday of the week can be calculated using the following formula, provided it starts the week on Monday:

=TODAY()-WEEKDAY(TODAY(),2)+1

When the second parameter of the WEEKDAY function is 2, Monday returns 1 and Sunday returns 7, so subtract it and add 1 to get this Monday. This Sunday is Monday plus 6.

Then the statistical formula is written as:

=COUNTIFS(C2:C200,">="&H2,C2:C200,"<="&H3)

There are two points to note here. First, "&" must be used after ">=" and "<=" to connect the symbol to the cell, and the cell address cannot be placed in double quotes. Second, the TODAY function changes every day. If you are making a weekly report and want to retain the results of a certain week, you should change H2 and H3 to fixed dates, otherwise the statistical interval will move after the table is reopened.

If you only want to count "before today" or a certain month, you only need to change the values of the two dates, and the formula structure remains unchanged.

Check each item after completion

After calculating the results of the formula, do not report them directly. The following items are prone to problems, please check them one by one.

Check items	How to see it	What should you do if there's a problem?
Check if the number of rows in the area is consistent	Compare the first and last row numbers of each area; they should be exactly the same	Unified to the same row number and rewrote the formula
Condition: Does the text match exactly?	Compare the differences between "Sales Department" and "Sales Department," paying attention to extra spaces	Use the TRIM function to clean up data, or switch to wildcards
Whether the date is the actual date	Select the date column to see if it is right-aligned, or check the values in the status bar	Use the column or replace function to convert to date format before counting
Whether the boundary date is included	Find a few records that happened to be on Monday and Sunday to see if they count	The validation formula uses ">=" and "<=", instead of ">" or "<".
Check whether the results match the screening	Use the filter function to manually count once and compare with the formula results	If inconsistent, verify each condition individually with COUNTIF

If the formula returns an incorrect value, in most cases it is due to inconsistent row counts in the range or incomplete parameter writing. If it returns 0, it is usually a condition text mismatch or the date is text. Both types of issues can be troubled using the methods above.

📊 Core Comparison

Several common multi-condition counting methods each have their own suitable scenarios:

Method	Suitable situations	Notes
COUNTIF	There is only one condition: quickly view a single indicator	Only one column can be checked; when adding conditions, COUNTIFS must be used instead
COUNTIFS	Two or more conditions are met simultaneously, including the date interval	The number of rows in the area must be consistent, and the conditions must have only a "and" relationship
pivot table	Frequent switching between dimensions and simultaneous viewing of reports with multiple groupings are required	After setting it once, you can view it repeatedly, but the data needs to be refreshed after updating

If you only count a few numbers at once, COUNTIFS just needs to write a formula. If you want to create long-term reports and frequently switch between departments and months, pivot tables are often more convenient.

❓ Frequently Asked Questions (FAQ)

1. How many conditions can COUNTIFS set at most?

COUNTIFS allows you to place multiple pairs of "Region plus Condition" settings, with the specific limit depending on your Excel version. In practice, two to three conditions can cover most statistical needs. If there are too many, it is recommended to split them into auxiliary columns for easier step-by-step verification.

2. Can COUNTIFS count colored cells?

COUNTIFS cannot be counted directly by cell color. A common approach is to add a column of manually annotated statuses, or use filtering to view the count results in the status bar and then combine it with other methods.

3. Why did the condition say ">60" without results?

This is usually because the numbers in the data column are in text format, or the condition is written incorrectly. First, make sure the numbers are right-aligned, then check if there are extra spaces between the comparison symbol and the number, and finally confirm the position of the double quotes is correct.

4. How do you write two non-adjacent date intervals?

You can calculate the two intervals separately and then add the results. If two intervals are in the same column, you can also write two sets of date conditions for the same area in COUNTIFS, but note that both conditions are met simultaneously; only rows that meet all conditions are counted, so it is usually better to count them separately.

5. Why do the statistics on this week's data change every day?

Because the formula uses the TODAY function, it automatically updates with the date. If you want to save the fixed results for a certain week, change the start and end dates to specific values, or copy and optionally paste them as values.

📝 Summary

COUNTIFS uses paired "region plus condition" to make multiple conditions apply simultaneously, suitable for scenarios such as statistically adding status or compiling weekly data by date interval. After writing the formula, remember to check the number of rows in the area, the date in the text format, and the boundary date, and confirm that the results match manual filtering.

Open OmniDoc and process your table file