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

```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, 2022 in Database 897 views

## 1 answer to this question.

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:

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.

• 13,630 points

## Selecting COUNT(*) with DISTINCT

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

## How to count distinct values in Excel

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

## Excel COUNTIF with multiple criteria and both row and column

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

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

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

## Can I have multiple primary keys in a single table?

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

## What is SELECT DISTINCT statement

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

## Inserting multiple rows in a single SQL query?

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