0

I have two tables (One that is used to capture every record, the other for capturing a summary of the records).

Tracker Table - Columns: Code, Date, Error Message

Summary Table - Columns: Code, Errors Count, Unique Errors

Currently, I use the following to retrieve the number of errors total from each app code in the Errors Count column: {=COUNT(IF([@Code]=Tracker[Code],1))}

i.e. If I have the following in the Tracker Table:

1234 | Dec 01 34 | Error 1111 has occurred in Application 1234

1234 | Dec 23 34 | Error 1111 has occurred in Application 1234

1234 | Dec 23 34 | Error 4444 has occurred in Application 1234

Then in the Summary Table I should have:

1234 | 3 | 2

I had a look at this answer (below formula), I can target the column in named table for the ranges (B2:B100, A2:A100), but I don't know what to use for the ROW(A2) target.

=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

What array formula do I use to find the unique number of each error?

Community
  • 1
  • 1
Bob.
  • 3,894
  • 4
  • 44
  • 76

1 Answers1

1

You can use this array formula:

=SUM(IFERROR(([@Code]=Tracker[Code])/COUNTIFS(Tracker[Error Message],Tracker[Error Message],Tracker[Code],[@Code]),0))

The basic approach is to divide 1 buy the number of times a certain Error Message appears and then SUM those... This will give you the unique number of messages.

We add in the initial [@Code]=Tracker[Code] so that we get a 1 or 0 to for the numerator (i.e. ignore those rows that don't match the Code) as well as the extra COUNTIFS condition ...,Tracker[Code],[@Code] so that we only count the occurrences of the Error Message with the matching Code

The IFERROR is to get rid of those pesky #DIV/0! errors for the unmatched rows...

Captain
  • 2,148
  • 15
  • 13