Technology

Simple and Efficient Way to Combine SUMIF and COUNTIF for High Productivity

The combination of SUMIF and COUNTIF has become familiar in every calculation function of Google Sheets. These two functions are used together to calculate and analyze complex functions, making tasks completed more efficiently. So how are these two functions used? Let's find out.

Administrator

15 February, 2024

153

The COUNTIF function is used to count the number of occurrences of a given value based on a specified condition. So, what can you do by combining SUMIF and COUNTIF in Google Sheets? Let’s find out in this article with FPT Shop.

How to use the SUMIF and COUNTIF functions in Google Sheets

To understand how to combine SUMIF and COUNTIF, it’s important to know how to use each function separately. Both of these functions are very popular and easy to use.

How to use the SUMIF function

The SUMIF function is used to calculate the total of a range of cells that meet a specified condition. The formula for the SUMIF function is: =SUMIF(range, criteria, sum_range), where range is the selected range containing the condition, criteria is the condition to be met, and sum_range is the range of cells to be summed.

How to use the COUNTIF function

COUNTIF is a conditional counting function used to count the number of occurrences of a given value based on a specified condition. The formula for this function is: =COUNTIF(range, criteria), where range is the data range to be counted and criteria is the condition to count the data in the selected range.

How to combine SUMIF and COUNTIF in Google Sheets

Both SUMIF and COUNTIF functions are commonly used, so they are often combined to obtain statistical data. The formula for combining SUM and COUNTIF is: =SUM(COUNTIF (range1, criteria1), COUNTIF (range2, criteria2)).

Here’s an example of using the SUM function combined with COUNTIF to calculate the total number of orders in the “Processing” and “Shipping” statuses. The formula is: =SUM(COUNTIF(A1:C7,”Processing”),COUNTIF(A1:C7,”Shipping”)). Press Enter to get the desired quantity.

Common errors when combining SUMIF and COUNTIF

When using any function, it’s common to encounter errors. Each error has different causes. Let’s take a look at some common errors when combining SUMIF and COUNTIF.

Error #N/A

The #N/A error occurs when the function cannot find the data required by the formula. This may be due to a missing or incorrect reference in the formula. To fix this error, simply remove the unnecessary comma.

Error #REF!

The #REF! error occurs when the formula refers to an invalid cell. This commonly happens when the referenced cells in the spreadsheet are incorrect. To fix this error, make sure to enter the correct data range.

Error #ERROR

This error means that Google Sheets couldn’t understand the formula you entered because it couldn’t analyze the syntax of the formula to perform the calculation. To fix this, make sure to add the semicolon in the correct syntax.

Error #VALUE

The #VALUE error occurs when the data type you use doesn’t match the formula you entered. For example, if the value “Shipping” is entered as a text but the formula expects a number, it will result in a #VALUE error. To fix this, enter a number value or convert the “Shipping” value to a number using the COUNTIF function.

Considerations when combining SUMIF and COUNTIF

When combining SUMIF and COUNTIF, if you want to use text in the formula, you need to use double quotation marks to extract the text. The criteria parameter is also a value that must be enclosed in double quotation marks. The function is case-insensitive. Each COUNTIF formula can only count based on one condition. The function will return incorrect results if you use a string longer than 255 characters.

Example of combining SUM and COUNTIF functions

Combining the SUM and COUNTIF functions to count the number of students in two classes, K19411 and K20411, participating in a race. The formula is:

=SUM(COUNTIF(A4:E453, “K19411”), COUNTIF(A4:E453, “K20411”)). The SUMIF function is used to count the number of students participating in each class, and the SUM function is used to calculate the total number of students participating in these two classes.

For example, if you want to calculate the total number of students registered for the race but did not participate in the race and the number of students who received prizes, the formula is:

=SUM(COUNTIF(E4:E453,”0″),COUNTIF(E4:E453,”>=90″))

The number of students who registered for the race but did not participate will have a running distance of 0, and the number of students who received prizes will have a running distance greater than or equal to 90. The COUNTIF function is used to count the number of students who did not participate and the number of students who received prizes. The SUM function is used to calculate the total quantity.

Frequently Asked Questions

Many people wonder if they can use the question mark and asterisk characters as wildcard characters in the search term when combining SUMIF and COUNTIF. You can use the question mark to represent a single character and the asterisk to represent a string of characters in the criteria parameter.

If you want to search for the question mark or asterisk characters themselves, you need to use a tilde (~) before the character.

When counting text values, does whitespace in the spreadsheet affect the result of combining SUMIF and COUNTIF? When counting text values, leading or trailing whitespace can cause inaccurate results in the SUMIF and COUNTIF functions.

Conclusion

In conclusion, through this article, we have learned more about how to combine SUMIF and COUNTIF in Google Sheets. By mastering these two useful functions, you will be able to easily and conveniently perform statistical calculations. With improved computer skills, you can study and work more effectively.