Is it possible to use Countifs to count only unique values based on a defferent data range

I apologize in advance if I am not posting this in the correct place, or if this is a duplicate, I have searched is much as possible but with no luck...

I trying to create dynamic report out of a work sheet I have and I am kind of stuck not able to get unique values.

In A1:A15000k I have Ticket/Task#, the ticket can be worked on more than once, i am trying to apply another criteria to my formula to only allow unique Ticket numbers to be counted.

here is the formula i am using now:

    =SUMPRODUCT(COUNTIFS(INDIRECT($C$3 & "!$G$1:$G$14989"),INDIRECT(D3),INDIRECT($C$3 & "!$S$1:$S$14989"),$B$3, INDIRECT($C$3 & "!$H$1:$H$14989"), E$10, INDIRECT($C$3 & "!$U$1:$U$14989"), ">="& $C$7, INDIRECT($C$3 & "!$U$1:$U$14989"), "<="& $D$7))

Non dynamic version of it

    =SUMPRODUCT(COUNTIFS($G$1:$G$14989,Names, $H$1:$H$14989, "Priority", $N$1:$N$14989, ">=3/15/2016", $N$1:$N$14989, "<=3/16/2017"))

where Names represent a set of users....

Sample data:
Excel.jpg

Thanks for all your help...

Do you want ticket numbers worked on more than once to only be counted once or not be counted at all?

So, only unique numbers or the first iteration of each number?

Counting duplicates as 1 would be

=SUMPRODUCT(($A$1:$A$100<>"")/COUNTIF($A$1:$A$100,$A$1:$A$100&""))

Change A to the column you are counting

For best results attach a SMALL sample file (about 20 rows worth of data) and tell us what result you expect.

Thanks @Tony Valko for your help, attaching sample file with this comment... The expected is to give me the count for unique ticket#, in the formula on the sheet that would be 27... and i am getting 33... hope this helps...

Sample.xlsx

Again thank you to all who have took time to look at this...

Why is it 27? It is 31? There are 31 different unique numbers.

You have 3 extra JJ-585 abd 1 extra CA200. In total you ahve 35 records. 35 - 4 = 31.

What error is it giving?

Two unique numbers do not match an other criteria on Column G, only counting Major issues not critical...

Just #N\A