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!

    ReplyDelete

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.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts