have seem some example of how to exclude duplicates but I've unfortunately been unable to implement then. I have two tables which I'm using the following COUNTIFS statement for:
=COUNTIFS(Table1[FILTERED_RFC_COVERAGE],"*" &Table2[@RFC]& "*",Table1[REL_NUM],[@[RELEASE_VERSION_NUMBER]])
This gives me a result that would be inaccurate because there is a good chance that the total is returning duplicates. A little explanation on the data:
Table1 contains (for simplicity sake) three columns:
Release Number | Test ID | RFC Number
53 | 1 | 1212, 1213
53 | 1 | 1212, 1213
53 | 2 | 1212
53 | 3 | 1213
53 | 4 | 1214, 1215
53 | 4 | 1214, 1215
Table 2 contains single RFC numbers per row:
RFC Number
1212
1213
1214
1215
Because RFC Number is a text field and can contain multiple RFCs, in Excel I'm essentially using a like operator to match an RFC number with the RFC numbers column, this is followed by ensuring the uniqueness of release. This currently gives me:
RFC Number | Tests
1212 | 3
1213 | 3
1214 | 2
1215 | 2
This is of course wrong, the correct representation would be:
RFC Number | Tests
1212 | 2
1213 | 2
1214 | 1
1215 | 1
This no longer double counts the same ID. I believe FREQUENCY or SUMPRODUCT could be used but if this can be explained a little that would be a great help.
Bookmarks