0

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....

enter image description here

OmarQA
  • 51
  • 4
  • If you would show some test data and expected output it would help us in understanding your problem better. – Scott Craner Mar 16 '17 at 13:32
  • @ScottCraner I have add a sample of the data, the expected result for the formula should be 29 Thanks in advanced for your taking the time to help... – OmarQA Mar 16 '17 at 13:49

1 Answers1

0

I gut this finally working but with a helper column... just added a new column to my original sheet that is checking duplicates n the column i don't want to count when its duplicated 

=IF(COUNTIF($C$6:$C6,C6)>1,"DUPLICATE",1)

After that all i needed to do a dd the new column to my countifs formula...

OmarQA
  • 51
  • 4