Hey Guys,
I have a rather long winded formula (which it might be difficult to keep up with), so i'll list the variables I require:
Filter by account - cell C2
Filter by product - cell C3
Quarter from - cell C4
Quarter to - cell C5
Year - cell C6
More data in an additional tab called "Database_sheet":
Column J - reference to the quarter
Column P - reference to the year
Column O - reference to account
Column L - reference to product
So I have a formula which counts all the references (Column DY in Database_sheet) depending on the filters as mentioned above (Cell C2-C6 filter criteria).
The only problem I have is that the same references are being doublecounted and there is no additional filter. Can someone help me count ONLY the unique values??
My formula currently is:
=IF(AND($C$3="TOTAL",$C$2="TOTAL"),COUNTIFS(Database_sheet!$DY:$DY,$B14,Database_sheet!$J:$J,">="&$C$4,Database_sheet!$J:$J,"<="&$C$5,Database_sheet!$P:$P,$C$6),IF(AND($C$3="TOTAL",$C$2<>"TOTAL"),COUNTIFS(Database_sheet!$DY:$DY,$B14,Database_sheet!$J:$J,">="&$C$4,Database_sheet!$J:$J,"<="&$C$5,Database_sheet!$O:$O,$C$2,Database_sheet!$P:$P,$C$6),IF(AND($C$3<>"TOTAL",$C$2="TOTAL"),COUNTIFS(Database_sheet!$DY:$DY,$B14,Database_sheet!$J:$J,">="&$C$4,Database_sheet!$J:$J,"<="&$C$5,Database_sheet!$L:$L,$C$3,Database_sheet!$P:$P,$C$6))))
I appreciate this is long winded, sorry! Would be grateful for any help!!
Bookmarks