Looking to us =COUNTIFS in which one criteria is unique

[morgankaufm]

I am tracking competition results in which competitors are required to compete in a category at three separate events in order to qualify for a finals. However, exhibitors can and often will compete multiple times in the same category at a single event.

The ranges I will be counting are shown in the sheet labeled "Exhibitor Results Report",
Column C - Member # (this is each competitors unique ID)
Column N - Horse # (this is each horses unique ID)
Column T - This is a unique code assigned to each event
Column CX - This is the class category
Column CY - A TRUE/FALSE indicating whether or not this event falls in the correct time period to count toward the finals

In the chart on the sheet labeled "Qualified" I need box to indicate the number of times the member in Column A competed with the horse in column C in the class category in Row 1 per each unique event. I currently have outlined a formula that takes into consideration all the criteria except for the unique event code.

=COUNTIFS('Exhibitor Results Report'!$C:$C,Qualifying!$A2,'Exhibitor Results Report'!$N:$N,Qualifying!$D2,'Exhibitor Results Report'!$CX:$CX,Qualifying!F$1,'Exhibitor Results Report'!$CY:$CY,TRUE)

Please let me know how I can accomplish this.

 

[Eric W]

Welcome to the MrExcel forum!

This is not tested, since I didn't want to retype all your data, but try this:

=ROWS(
 UNIQUE(
 FILTER('Exhibitor Results Report'!$T$2:$T$10,
('Exhibitor Results Report'!$C$2:$C$10=Qualifying!$A2)*
('Exhibitor Results Report'!$N$2:$N$10=Qualifying!$D2)*
('Exhibitor Results Report'!$CX$2:$CX$10=Qualifying!F$1)*
('Exhibitor Results Report'!$CY$2:$CY$10=TRUE))))

Change the bottom row of course to match the bottom row of your table.