COUNTIFS function - Zoho Sheet - Help Guide

COUNTIFS

Counts the number of times all the given criteria are met in the given cell ranges.

 

Syntax


COUNTIFS(test_range; condition; [test_range1]; [condition1]; ...)  

test_range: The ranges you want to test for the given criteria. Eg., B2:B8

condition: The condition may be:

A number, such as 15

An expression, such as 2/3 or SQRT(A1)

A text string, such as "zoho"

Other than testing for similar values in test_range for the given condition, COUNTIF function also allows the use of >, <, >=, <=, =, <> in the condition.

Remarks

  • The additional test_ranges provided must be of the same size as that of the first given test_range.

  • The COUNTIFS function is not case-sensitive i.e. APPLE and Apple are treated similar.


Examples

     

Formula

Result

=COUNTIFS( B2:B8 ; ">300" ; A2:A8 ; "Lockers" )

2


=COUNTIFS(E2:E8;">20";E2:E8;"<30")
3

   

In example 1, the given conditions are to count the number of cells where the number of units of "Lockers" sold is greater than 300. Although there are three cells containing the term "Lockers", only 2 is returned as the result, because one of the units sold is lesser than 300.


Get a hands-on experience of the function in the embedded range below.



Possible Errors

 

Errors

Meaning

#N/A!

  • The function could not find the value it was asked to look for. Commonly occurs in VLOOKUP and similar functions.

#NAME!

  • The function name is incorrect or invalid.

  • The given defined name (if any) is invalid.

  • There is a typo in the defined name used in the function.

  • Double quotes are missing for text values in the function.

  • Colon missing in a cell range reference.

#VALUE!

  • The given argument's data type is incorrect or invalid.
    For example, the function =DATE(A1:A3) will return a #VALUE error when a text value is present in the range.

#REF!

  • The given cell reference is incorrect or invalid. This error may occur when a cell/range has been deleted, moved, or pasted over.

 

Similar Functions