www.exceldemy.com
Open in
urlscan Pro
2606:4700:3030::ac43:96e1
Public Scan
URL:
https://www.exceldemy.com/excel-countifs-unique-values-with-criteria/
Submission: On December 29 via manual from IN — Scanned from DE
Submission: On December 29 via manual from IN — Scanned from DE
Form analysis 4 forms found in the DOM
GET https://www.exceldemy.com/
<form role="search" method="get" class="search-form" action="https://www.exceldemy.com/">
<input type="text" name="s" placeholder="Search" data-posttype="post">
<input type="hidden" name="post_type" value="post"> <button type="submit" class="btnsearch hideonmobile" aria-label="Search"><i class="rhicon rhi-search"></i></button>
</form>POST https://www.exceldemy.com/wp-comments-post.php
<form action="https://www.exceldemy.com/wp-comments-post.php" method="post" id="commentform" class="comment-form"><textarea id="comment" name="comment" cols="30" rows="10" aria-required="true"></textarea>
<div class="usr_re"><input id="author" type="text" value="" name="author" placeholder="Name"></div>
<div class="email_re"><input id="email" type="text" value="" name="email" placeholder="E-mail"></div>
<div class="site_re end"><input id="url" type="text" value="" name="url" placeholder="Website"></div>
<div class="clearfix"></div>
<p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="Submit"> <input type="hidden" name="comment_post_ID" value="106222" id="comment_post_ID">
<input type="hidden" name="comment_parent" id="comment_parent" value="0">
</p>
<p style="display: none;"><input type="hidden" id="akismet_comment_nonce" name="akismet_comment_nonce" value="fd86933b3e"></p>
<p style="display: none !important;"><label>Δ<textarea name="ak_hp_textarea" cols="45" rows="8" maxlength="100"></textarea></label><input type="hidden" id="ak_js_1" name="ak_js" value="38">
<script type="rocketlazyloadscript">document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() );</script>
</p>
</form>GET https://www.exceldemy.com/
<form role="search" method="get" class="search-form" action="https://www.exceldemy.com/">
<input type="text" name="s" placeholder="Search" data-posttype="post">
<input type="hidden" name="post_type" value="post"> <button type="submit" class="btnsearch hideonmobile" aria-label="Search"><i class="rhicon rhi-search"></i></button>
</form>GET https://www.exceldemy.com/
<form role="search" method="get" class="search-form" action="https://www.exceldemy.com/">
<input type="text" name="s" placeholder="Search" data-posttype="post">
<input type="hidden" name="post_type" value="post"> <button type="submit" class="btnsearch hideonmobile" aria-label="Search"><i class="rhicon rhi-search"></i></button>
</form>Text Content
* Home * About Us * Blog * Submit Excel Problem * Excel Consultancy * Excel Training * Excel Books * Learn VBA * Learn PivotTable * Excel Practice * Advanced Excel Exercises * Calculation with Excel Formulas * Excel Pivot Table Exercises * Excel Training & Courses * Interview Questions with Excel Topics * MCQ Questions on MS Excel * Mock Test for MS Excel Certification * Practice Test & Quiz * Practice with Power BI and Power Pivot * Practice with Power Query * Practice with VBA * Use of Excel Functions Home » Excel Formulas » Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples) Excel Formulas COUNT UNIQUE VALUES WITH CRITERIA BY COUNTIFS IN EXCEL (4 EXAMPLES) Sabrina Ayon Oct 24, 2022 0 While working with data in Microsoft Excel, sometimes we need to find the unique values. The main goal of counting separate and special values is to distinguish them from duplicates in an Excel list. We can count the unique values with so many functions and different formulas for different purposes. In this article, we will count unique values with criteria by the COUNTIFS function in Excel. -------------------------------------------------------------------------------- Table of Contents hide Download Practice Workbook Introduction to COUNTIFS Function 4 Different Examples to Count Unique Values with Criteria by COUNTIFS in Excel 1. Estimate Unique Values Based on a Particular Criteria in Excel 2. Multiple Criteria to Count Unique Excel Values 3. Different Number of Text Values Counting in Excel 4. Count Numeric Values That Aren’t the Same Conclusion Related Articles DOWNLOAD PRACTICE WORKBOOK You can download the workbook and practice with them. MY LATEST VIDEO Necessary-Excel-Skills-to-Get-Hired 0 seconds of 1 minute, 30 secondsVolume 0% Press shift question mark to access a list of keyboard shortcuts Keyboard ShortcutsEnabledDisabled Play/PauseSPACE Increase Volume↑ Decrease Volume↓ Seek Forward→ Seek Backward← Captions On/Offc Fullscreen/Exit Fullscreenf Mute/Unmutem Seek %0-9 Live 00:00 01:30 01:30 COUNTIFS Unique Values.xlsx -------------------------------------------------------------------------------- INTRODUCTION TO COUNTIFS FUNCTION In Excel, the COUNTIFS function counts the number of cells in a range that match one of the provided conditions. * Syntax The syntax for the COUNTIFS function is: COUNTIFS (range1, criteria1, [range2], [criteria2], …) * Arguments range1: [required] This is the first range to be evaluated. criteria1: [required] The range1 criteria to employ. range2: [optional] This is the second range to be evaluated. criteria2: [optional] The range2 criteria to utilize. * Return Value The total number of times a set of criteria has been met. -------------------------------------------------------------------------------- 4 DIFFERENT EXAMPLES TO COUNT UNIQUE VALUES WITH CRITERIA BY COUNTIFS IN EXCEL To count unique values with criteria by using the COUNTIFS function in Excel, we are going to use the following dataset. The dataset contains some Product names in column B, the Brand of each product in column C, the names of the Customers who brought those products in column D, and the Contact Address for each customer in column E. Now, we want to count the unique values with different criteria, so, let’s demonstrate the examples of those by using this dataset. -------------------------------------------------------------------------------- 1. ESTIMATE UNIQUE VALUES BASED ON A PARTICULAR CRITERIA IN EXCEL We can count the number of unique values based on a single criterion by combining the SUM, IF, and COUNTIFS functions in Excel. So, let’s follow the procedure for this. STEPS: * Firstly, select the cell where you want to count the unique values using the criteria. Se, we select cell H5. * Secondly, put the formula in that cell. =SUM(IF(G5=$D$5:$D$13, 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13)), 0)) * Finally, pressing Enter will show the result. 🔎 How Does the Formula Work? ⏩ G5=$D$5:$D$13: This will find the cells containing Jhon, as cell G5 contains Jhon. ⏩ COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13: For all addresses that exist just once, will return TRUE; for all addresses that repeat multiple times, return FALSE. ⏩ 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13): This will divine the formula by 1 and returns 0.5. ⏩ IF(G5=$D$5:$D$13, 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13)), 0): This will compare the conditions of the formula are met or not if they are met then returns 1, 0 otherwise. ⏩ SUM(IF(G5=$D$5:$D$13, 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13)), 0)): This will count the total unique values. Read More: How to Count Unique Values in Multiple Columns in Excel (5 Ways) -------------------------------------------------------------------------------- 2. MULTIPLE CRITERIA TO COUNT UNIQUE EXCEL VALUES We can use multiple criteria to count the unique values. So, here the criteria are Customer Name, Brands, and we will count the products if they fulfill those criteria. Mainly by using the COUNTIFS function we will count only those products whose customer names and brands are the same. So, let’s see the following steps. STEPS: * By the same token as before, select the cell where you want the result. So, we select cell I5. * Next, type the formula in that particular cell. =COUNTIFS(D5:D13,G5,C5:C13,H5) * Now, press Enter. Here, the range of cells D5:D13 indicates the Customer Name, and the criteria for this range is G5 which is Jhon. Also, the range of cells C5:C13 indicates the Brand, and the criteria for this range is H5 which is Asus. Read More: How to Count Unique Names in Excel (5 Methods) -------------------------------------------------------------------------------- 3. DIFFERENT NUMBER OF TEXT VALUES COUNTING IN EXCEL We can count different numbers of unique text values by combining the SUM, ISTEXT, and COUNTIFS functions in Excel. Now, we will use the COUNTIFS function to count the number of distinct text values from the contact addresses. Here, the criteria are the text values of the Contact Address column. We will count the unique text address in cell G5. Let’s look at the procedure to do this. STEPS: * First, select the cell where you want to count the unique values using the criteria which is the text value. So, we select cell G5. * Second, put the formula in that cell to show the result. =SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1)) * Finally, press Enter. So, there are a total of 2 unique text values in there. 🔎 How Does the Formula Work? ⏩ ISTEXT(E5:E13): This will return TRUE for all addresses that are text values, will return FALSE otherwise. ⏩ COUNTIFS(E5:E13,E5:E13): Here, this will returns TRUE for all addresses that appear just once and will return FALSE for all addresses that appear more than once. ⏩ ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13): This will multiply the two formulas and returns 1 if they are met, returns 0 otherwise. ⏩ SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1)): This will return the unique text values. NOTE: It’s a formula that uses an array. Unless you’re using Office 365, remember to hit Ctrl + Shift + Enter. Read More: COUNTIFS Unique Values in Excel (3 Easy Ways) -------------------------------------------------------------------------------- 4. COUNT NUMERIC VALUES THAT AREN’T THE SAME We can use Excel SUM, ISNUMBER, and COUNTIFS functions in conjunction to count unique numeric values in excel. Here, the criteria are that the combination of those three functions will count only the numerical values from a range of cells. Now, let’s see the steps down. STEPS: * In the beginning, choose the cell where you wish to count the unique values based on the numerical value as the criteria. As a result, we choose cell G5. * Second, enter the formula to display the result in that cell. =SUM(--(ISNUMBER(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1)) * And, press the Enter button on your keyboard. 🔎 How Does the Formula Work? ⏩ ISNUMBER(E5:E13): For all the addresses that are numeric values, this will return TRUE, FALSE otherwise. ⏩ COUNTIFS(E5:E13,E5:E13): For all addresses that show just once, this will return TRUE and return FALSE for all addresses that show more than once. ⏩ ISNUMBER(E5:E13)*COUNTIFS(E5:E13,E5:E13): This will multiply the ISNUMBER formula & COUNTIFS formula. Then will return 1 if they are met, return 0 otherwise. ⏩ SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1)): The unique number values will return. Read More: Excel VBA: Count Unique Values in a Column (3 Methods) -------------------------------------------------------------------------------- CONCLUSION Using those above methods you can count unique values with criteria in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog! -------------------------------------------------------------------------------- RELATED ARTICLES * How to Count Unique Values in Filtered Column in Excel (5 Methods) * Count Unique Values Based on Criteria in Another Column in Excel * How to Count Unique Values in Excel Using Pivot Table * Count Unique Values with Criteria by SUMPRODUCT in Excel SaveSavedRemoved 0 Tags: COUNTIFS FunctionCOUNTIFS Function in ExcelExcel Count Unique ValuesIF FunctionISNUMBER FunctionISTEXT FunctionSUM Function SABRINA AYON Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you. Related Articles Added to wishlistRemoved from wishlist 0 HOW TO USE CONDITIONAL FORMATTING WITH IFERROR IN EXCEL Added to wishlistRemoved from wishlist 0 HOW TO CALCULATE HISTORICAL VOLATILITY IN EXCEL (WITH EASY STEPS) Added to wishlistRemoved from wishlist 0 HOW TO USE AND AND OR FUNCTIONS IN EXCEL (3 PRACTICAL EXAMPLES) Added to wishlistRemoved from wishlist 0 HOW TO FIND DISTANCE BETWEEN ZIP CODES IN EXCEL USING FORMULA We will be happy to hear your thoughts LEAVE A REPLY CANCEL REPLY Δ Affiliate Disclosure This post may contain affiliate links, meaning when you click the links and make a purchase, we may earn an affiliate commission, but this never influences our opinion. 100+ Pre-Built Excel Templates About ExcelDemy.com ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems. ” title=” Search in ExcelDemy.com Follow us Facebook | LinkedIn | Twitter | YouTube 2013-2022 ExcelDemy.com. All rights reserved. * * Home * About Us * Blog * Submit Excel Problem * Excel Consultancy * Excel Training * Excel Books * Learn VBA * Learn PivotTable * Excel Practice * back * Advanced Excel Exercises * Calculation with Excel Formulas * Excel Pivot Table Exercises * Excel Training & Courses * Interview Questions with Excel Topics * MCQ Questions on MS Excel * Mock Test for MS Excel Certification * Practice Test & Quiz * Practice with Power BI and Power Pivot * Practice with Power Query * Practice with VBA * Use of Excel Functions * Update Privacy Preferences An Elite CafeMedia Tech Publisher