## 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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

#### 1 comment:

1. You need to take part in a contest for one of the finest websites on the net.
I am going to recommend this blog!

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.