CountifS multiple criteria distinct count

0 votes

I'm looking for a formula calculating : distinct Count + multiple criteria Countifs() does it but do not includes distinct count...

Here is an example.

I have a table on which I want to count the number of distinct items (column item) satisfying multiple conditions one column A and B : A>2 and B<5.

Image description here

enter image description here

Line  Item  ColA  ColB
1     QQQ    3     4
2     QQQ    3     3
3     QQQ    5     4
4     TTT    4     4
5     TTT    2     3
6     TTT    0     1
7     XXX    1     2
8     XXX    5     3
9     zzz    1     9

Countifs works this way : COUNTIFS([ColumnA], criteria A, [ColumnB], criteria B)

COUNTIFS([ColumnA], > 2 , [ColumnB], < 5)

Returns : lines 1,2,4,5,8 => Count = 5

How can I add a distinct count function based on the Item Column ? :

lines 1,2 are on a unique item QQQ

lines 4,5 are on a unique item TTT

Line 8 is on a unique item XXX

Returns Count = 3

How can I count 3 ?!

Thanks

Apr 4 in Database by gaurav
• 22,040 points
649 views

1 answer to this question.

0 votes

Please enter this formula into a blank cell where you want to get the result, G2, for instance:

=SUM(IF("Tom"=$C$2:$C$20, 1/(COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20)), 0)), and then press Shift + Ctrl + Enter keys together to get the correct result, see screenshot:

doc count unique with multiple criteria 2

Note: In the above formula, "Tom" represents the name criteria you want to count, C2:C20 represents the cells that include the name criterion, and A2:A20 represents the cells where you want to count the unique values.

answered Apr 4 by Edureka
• 13,640 points

Related Questions In Database

0 votes
0 answers

Selecting COUNT(*) with DISTINCT

In SQL Server 2005, I have a ...READ MORE

Feb 14 in Database by Neha
• 8,940 points
127 views
0 votes
1 answer

How to count distinct values in Excel

Use functions to count the number of ...READ MORE

answered Mar 15 in Database by gaurav
• 22,040 points
524 views
0 votes
1 answer

Excel COUNTIF with multiple criteria and both row and column

Count Cells Based On Text Value Using ...READ MORE

answered Mar 25 in Database by gaurav
• 22,040 points
345 views
0 votes
2 answers

What are the ways to get the count of records in a table

With the help of the SQL count ...READ MORE

answered Aug 20, 2020 in Database by Okugbe
• 280 points
2,468 views
0 votes
1 answer

Can I have multiple primary keys in a single table?

A Table can have a Composite Primary Key which ...READ MORE

answered Oct 25, 2018 in Database by Frankie
• 9,830 points
2,642 views
0 votes
1 answer

What is SELECT DISTINCT statement

This statement is used to return only ...READ MORE

answered Nov 21, 2018 in Database by Sahiti
• 6,380 points
303 views
0 votes
1 answer

Updating multiple records in this year with information held in previous year.

Hello, For your query you can refer this:https://www.plus2net.com/sql_tutorial/sql_update.php Hope ...READ MORE

answered Nov 9, 2020 in Database by Niroj
• 82,800 points
330 views
0 votes
1 answer

Inserting multiple rows in a single SQL query?

In SQL Server 2008, multiple rows can ...READ MORE

answered Feb 10 in Database by Vaani
• 7,020 points
156 views
0 votes
1 answer

How to transform multiple tables in one excel sheet to one table with Power BI?

If my prediction is correct- You have a ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
887 views
0 votes
1 answer

Excel - how to calculate sum of multiple rows into different columns

The AutoSum button or formula can be ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
3,734 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP