Hi all
I am trying to write a formula in several cells to count unique records and to return the count of distinct records or sum of.
But not having any luck as there are variations on what I have seen and not making sense to me.
The table below provides the data.
Would really appreciate if someone could provide the formulas for X, Y and Z, please?
Additionally, if the formula for X, Y and Z are in one sheet (lets say Worksheet 1) and the data source if from another sheet (Worksheet 2) of the same file, would the formula be different? If so, would also appreciate to know how the formula is to be written?
Thanks to you all in advance.
S
I am trying to write a formula in several cells to count unique records and to return the count of distinct records or sum of.
But not having any luck as there are variations on what I have seen and not making sense to me.
The table below provides the data.
Column 1 | Column 2 | Column 3 | Column 4 |
350350 | 51110754 | 247745 | 0 |
350350 | 51116014 | 0 | |
350350 | 51117956 | 247942 | 1 |
350350 | 51124999 | 248107 | 0 |
350350 | 51126121 | 248136 | 0 |
350350 | 51127311 | 248176 | 0 |
350350 | 51127311 | 248177 | 1 |
350350 | 51127311 | 248177 | 0 |
350350 | 51132166 | 0 | |
350350 | 51134389 | 248355 | 1 |
350350 | 51137817 | 248458 | 0 |
350350 | 51138092 | 248462 | 0 |
350350 | 51140089 | 0 | |
350350 | 51140813 | 0 | |
350350 | 51141228 | 248540 | 0 |
350350 | 51141964 | 248549 | 1 |
350350 | 51142639 | 0 | |
350350 | 51147527 | 248677 | 1 |
350350 | 51151164 | 248760 | 0 |
350350 | 51152255 | 248793 | 1 |
350350 | 51154526 | 248846 | 0 |
350350 | 51156813 | 248911 | 1 |
350350 | 51156873 | 248918 | 0 |
350350 | 51160742 | 249023 | 0 |
350350 | 51161057 | 0 | |
350350 | 51161167 | 249032 | 0 |
350350 | 51163084 | 249088 | 0 |
350350 | 51163104 | 249087 | 0 |
350350 | 51163145 | 249091 | 1 |
350350 | 51165697 | 249162 | 0 |
350350 | 51165728 | 0 | |
350350 | 51168150 | 249233 | 0 |
350350 | 51170345 | 249308 | 0 |
350350 | 51171401 | 249335 | 0 |
350350 | 51172742 | 0 | |
350350 | 51176634 | 0 | |
350350 | 51177341 | 0 | |
350350 | 51177538 | 0 |
Where Column 1 = 350350, then return the count of unique or distinct records from Column 2 and not to count any blank fields in Column 2 | Where Column 1 = 350350, then return the count unique or distinct records from Column 3 and not to count any blank fields in Column 3 | Where Column 1 = 350350 and for where Column 3 are only unique or distinct where Column 4 is 1, then return the sum of Column 4 for the unique or distinct records in Column 3 where its corresponding record in Column 4 is 1 . |
X | Y | Z |
The result should return 37 | The result should return 26 | The result should return 8 |
Would really appreciate if someone could provide the formulas for X, Y and Z, please?
Additionally, if the formula for X, Y and Z are in one sheet (lets say Worksheet 1) and the data source if from another sheet (Worksheet 2) of the same file, would the formula be different? If so, would also appreciate to know how the formula is to be written?
Thanks to you all in advance.
S