How to use the Excel COUNTIFS function

May 6, 2021

3 Min Read

126 Views

When working with large data to extract the information you need to create a summary report. Countif, sum if are the two basics we need to learn about it. it can take up to very large criteria to work with. Let’s look at How to use the Excel COUNTIFS function and SUMIF Fucntions. CountIfs are available from Excel 2007 versions

How to work with COUNT Function in Excel

The count is used to evaluate how many values are present in the column, for example counting the total number package received on a specific date, this would be much easier if we used Named ranges. To use the count function select the cell, Go the Formulas Tab then select create from selection, uncheck the left column in the pop-up, and click on Okay.

COUNT Function: Counts all the occurrences of Numeric value
COUNTA Function: Irrespective of numeric, and alphabet value it counts occurrences
COUNTBLANK Function: Counts the blank values in a given range

=count(B2:B7)       Excel will return 4 as ouptut
=countblank(B2:B7)  Excel will return 2 as output
=countA(col)        Excel will return 5 as output

What is COUNTIFS Function in Excel

The above COUNT function we used as a whole without any criteria, Now COUNTIFS will help to get the count based on certain criteria. Criteria can be number, text if criteria are expression should be specified in quotation mark. There are also wild card characters like? These are allowed to use as criteria,? used to match with the character in the criteria.

COUNTIFS(Name range, Crietria1)
=COUNTIFS(Name,A1)
=COUNTIFS(Year,2021)
=COUNTIFS(State,"Kerala") #for state = Kerala
=COUNTIFS(Year,">2011")  #for year greater than 2011

COUTNIF(B2:B7, 5)    returns the value 1 as only 5$ occurances
COUTNIF(B2:B7, ">1")    returns the value 3 as satisfy the condition, Quotation is important here.
COUTNIF(B2:B7, <>1)     Not equal to 1
COUTNIF(B2:B7, "<>")     Not blank
COUTNIF(B2:B7, "")      Is blank

SUMIFS Function in Excel

It will count total values in the table. Helps to summarise the Data, order of selecting the range is one thing to keep in mind. Similar to COUNTIF formula criteria can be added to this. One disadvantage of SUMIF is it only supports one condition. Any Characters must be quoted with ” “.

SUMIFS(range, criteria)
SUMIF(A1:A10, ">1.2")    returns value greater than 100

=SUMIFS(B2:B7,A2:A7,"Flipkart") returns 3.4 by satisfying the condition.

A small sneak peak to Absolute Cell Referencing here, can use shortcut Key F4 Multiple Times to make a switch.
$B1 : Coloum stagnet when copy
$B$1 : Coloum and row dont change when copy : For Example When i do cell filling, i want B1 coloumn to refer in all other cell.
B$1 : Row, coloumn dont change when copy

FINAL VERDICT :

In conclusion, I hope you enjoyed reading this article on “How to use the Excel COUNTIFS function”. You can refer to our Excel Tutorials for beginners to learn other Excel functions. Signing off Sanjay Kv

Last Update: May 9, 2021