COUNTIFS and SUMIFS Excel Functions Explained

Last Updated on: 6th February 2024, 06:26 pm

Hey! This is the simple understandable explanation for COUNTIFS and SUMIFS Excel functions with examples. You will understand the use of both formulas easily.

COUTIFS

The COUNTIFS function in Excel is used to count the number of cells that meet multiple criteria. It allows you to specify multiple conditions, and it counts only the cells that meet all the specified conditions. The general syntax of the COUNTIFS function is:

=COUNTIFS(range1, criteria1, range2, criteria2, ...)

Here’s an explanation of the components of the formula:

• range1, range2, …: These are the ranges of cells you want to evaluate for the given criteria.
• criteria1, criteria2, …: These are the conditions or criteria that must be met for the cells to be counted.

Now, let’s go through an example to illustrate how to use COUNTIFS:

Suppose you have a dataset with sales data in column A and corresponding product names in column B. You want to count the number of sales for the product “Apple.”

1. Data Setup:

   |   A   |      B      |
   |-------|-------------|
   | Sales |  Product    |
   | 100   |  Apple      |
   | 150   |  Orange     |
   | 120   |  Apple      |
   | 80    |  Banana     |
   | 200   |  Apple      |

2. COUNTIFS Formula:
   In a cell where you want the result, you would enter the following formula:

   =COUNTIFS(B2:B6, "Apple")

This formula counts the number of occurrences of “Apple” in column B.

3. Result:
   The result of the formula in this example would be 3, as there are three instances of “Apple” in the specified range (B2:B6).

You can also use multiple criteria with COUNTIFS. For example, if you want to count the number of sales for “Apple” where the sales are greater than 100:

=COUNTIFS(B2:B6, "Apple", A2:A6, ">100")

This formula counts the number of occurrences where the product is “Apple” and the corresponding sales value is greater than 100.

Remember to adjust the ranges and criteria based on your specific data and conditions.

SUMIFS

To sum the sales numbers for the item “Apple” across different columns in Excel, you can use the SUMIFS function.

Assuming your data is in columns A to Z and the sales data is in column B:

1. Open your Excel spreadsheet:
   Open the Excel file containing your data.
2. Identify the range:
   Identify the range where your data is located. For example, if your data is in columns A to Z, and the rows are from 1 to 100, you can use the range A1:Z100.
3. Write the SUMIFS formula:
   In a cell where you want the result, use the SUMIFS function. The general syntax of SUMIFS is:

   =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

In this case, the sum_range is the sales column (e.g., B1:B100), criteria_range1 is the column where you want to check for the item name (e.g., A1:Z100), and criteria1 is the specific item you are interested in (e.g., “Apple”).

The formula might look like this:

   =SUMIFS(B1:B100, A1:Z100, "Apple")

This formula will sum the sales numbers in column B where the corresponding item in the same row matches “Apple” across columns A to Z.

4. Press Enter:
   After entering the formula, press Enter.

Adjust the column references and range based on your specific data layout. This formula will sum the sales numbers for the item “Apple” across different columns.