excelif-statementcountduplicates# COUNTIFS in a spreadsheet while ignoring duplicates in a separate column

I'd like to find how to use the COUNTIFS function in excel to count the rows in a spreadsheet while ignoring duplicate values in a separate column. Here is a sample of the data:

[]

I would like to count the number of rows that are "One" on status and "Blue" on category. However, I only want to count the duplicates for these respective values in "ID" once.

Here's what I've tried:

```
=SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)>0,1))
```

Same problem as the previous example. - this works for giving me the number of duplicates in column A, but I can't work any IF statements in effectively.

```
=IF(B2:B12="One",IF(C2:C12="Blue",SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)>0,1))))
```

When I add IF statements in, I get the same number as the earlier formula.

For your copy/pasting pleasure:

```
ID Status Category
1423 One Blue
1423 One Blue
1423 One Red
5124 One Blue
5124 One Blue
2341 Two Blue
1111 One Red
1212 Two Red
1212 One Blue
1111 One Red
5124 One Red
```

The ideal result would be **3**.

Solution

There is a standard COUNTUNIQUE method using SUMPRODUCT and COUNTIF functions; (e.g. `SUMPRODUCT(1/COUNTIF(A2:A12, A2:A12&""))`

). This can be expanded to include conditions by changing to a COUNTIFS function but care must be made to ensure that no `#DIV/0!`

error can occur.

In E4 as a standard formula,

```
=SUMPRODUCT(((B2:B12="one")*(C2:C12="blue"))/(COUNTIFS(A2:A12, A2:A12, B2:B12, "one", C2:C12, "blue")+((B2:B12<>"one")+(C2:C12<>"blue"))))
```

The numerator of the division operation provides half of the conditional processing. The COUNTIFS denominator of the division operator must be the opposite (non-zero) of the numerator when the numerator is zero.

- Excel: restore (or don't cut) borders from cells during cut and paste
- SetFocus has no effect in Userform for Textbox
- How can I setup Pagination in Excel Power Query?
- Ability to retrieve the encoded value when the insert event is triggered in the browser
- Adding codes to the newly added tab
- Automatically change cell reference in formulas, based off of cell updating
- Invalid parameter error when method is called from another sub
- How to open file on my Onedrive with VBA if I don't have internet connection?
- Separate letters in excel from 1 column to different rows
- Store the existing value and the new input value and calculate a result
- VBA Workbooks stop working with upgrade to Excel 365 - VBA Compiling Issue?
- ComboBox error: Unable to get the match property of the worksheetfunction class
- Excel search function problem when trying to display only certain columns
- Countif rows are within 1,000 milliseconds or 1 second
- Unique list using first word in a string
- Issue saving changes made in excel by python
- excel formula for searching string across columns and filtering results
- VBA Code for Multi Drop Down Auto-Filters in Worksheet Follow Up New Question
- Can Excel retain 2 paste special options for a linked cell?
- Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
- Is there a way to combine COUNTIF and FILTER?
- Powershell Excel Autofilter RGB Colors
- Registering COM Object on Windows 64-bit For Use In Excel-32 Bit Is Failing
- Shift data in excel Sheet to left to remove empty cells and include Header names
- If statement in Worksheet_Change not working with On Error Resume Next
- is there a excel formula which will give me the highest year from column F, corresponding to every company code in column c
- Lookup and merge data
- Parse Full Name Into Parts
- Transfer data to other sheets/ranges based on dropdown menu on home page
- Excel formula to check a text value from a cell against table's first column and return a value from the table second column