## Wednesday, 13 April 2016

### Excel Countifs Function to Find the Count based on Multiple Criteria

How to Find the Count based on Multiple Criteria in Excel
Suppose we have the Data as follows :

From the above data we can find the Count based on Multiple Criteria on Single Column and Multiple Columns as follows :
Single Column Criteria :
Now from the above data , if you wants to Find the Count of Activity Id where [Activity Status] = "Completed" or "Failed" , we can use the below formula :

=SUM(COUNTIFS(INDIRECT("\$B\$1:\$B\$"&COUNTA(\$A:\$A)),{"Completed","Failed"}))

Result = 8

Multiple Columns Criteria :
Now from the above data , if you wants to Find the Count of Activity Id where [Activity Status] = "Completed" or "Failed" ,and [Email Notification]="Not Yet Sent" or "Not Sent" or "Not Delivered" , can use the below formula :

=SUM(COUNTIFS(INDIRECT("\$B\$1:\$B\$"&COUNTA(\$A:\$A)),{"Completed","Failed"},INDIRECT("\$C\$1:\$C\$"&COUNTA(\$A:\$A)),{"Not Yet Sent";"Not Sent";"Not Delivered"}))

Result = 3

Notes :
--SUM function Sums up the counts of individual Criteria.
--INDIRECT("\$B\$1:\$B\$"&COUNTA(\$A:\$A)) function creates the dynamic used range in the Column B.
--Please be make sure that, in Multiple Column Criteria, we need to use the ;(Semicolon) to separate the Criteria items in other than First Criteria where we use ,(Comma).

Thanks,
TAMATAM