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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts