Forum Discussion
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!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.