How to add unique values ​​in relation to mixed data present on other columns?

Copper Contributor

How to add unique values ​​in relation to mixed data present on other columns?

Hi everyone,
starting from a database page, I need to calculate how many files have been worked on in a particular day.

I'll explain how the database is made:

- In column A, I have some serial numbers which may repeat, but for every number I'm only interested in 1 serial worked per day.
- In column L, I have the date in which a serial number was worked on.
- In column O, I have the teams that received the requests (among the others, I need to take into consideration only 1. Let's call it "YELLOWS").
- In column P, I have the team to which the person who worked on the file belongs (in the database, among those present in the column, I need to take into consideration only 3. Let's call them YELLOW - the same as the original - RED, and GREEN).

Basically I need to know on day "xx.xx.xxxx" (column L), how many unique numbers of practices (column A) that arrived at the YELLOWS (Column O), the Yellows, Reds and Greens teams have worked (Column P).

Is it possible?

Re: How to add unique values ​​in relation to mixed data present on other columns?

So far I understand, you want to count the unique serial numbers worked on by the "YELLOWS," "RED," and "GREEN" teams for each day.

You can use the COUNTIFS function in Excel to achieve this.

Assuming your data starts from row 2 (with headers in row 1), you can use the following formula in a separate cell, let us say in cell Q2:

=SUM(COUNTIFS(L:L, "xx.xx.xxxx", O:O, "YELLOWS", A:A, UNIQUE(A:A)), COUNTIFS(L:L, "xx.xx.xxxx", P:P, "YELLOW", A:A, UNIQUE(A:A)), COUNTIFS(L:L, "xx.xx.xxxx", P:P, "RED", A:A, UNIQUE(A:A)), COUNTIFS(L:L, "xx.xx.xxxx", P:P, "GREEN", A:A, UNIQUE(A:A)))

Formula is untested.

This formula uses COUNTIFS to count the unique serial numbers for each team based on the specified date. Replace "xx.xx.xxxx" with the actual date you are interested in.

Please note that the UNIQUE function is a dynamic array function available in newer versions of Excel. If you are using an older version that does not support this function, you might need to create a helper column to get unique serial numbers.

Also, if your data spans a large range, you might want to adjust the column references accordingly.

Formula is untested.

My answers are voluntary and without guarantee!