Can anyone please help me figure out the right formula for the below problem?

I want a count without"sample" The right answer is 3 but formula return 4

enter image description here

=COUNTUNIQUEIFS('PD01'!B2:B17,'PD01'!C2:C17,"PD01",D2:D17,"NEW",'PD01'!A2:A17,J2) COUNTUNIQUEIFS('PD01'!B2:B17,'PD01'!C2:C17,"PD01",D2:D17,"",'PD01'!A2:A17,J2)                                                                                   

Thanks a lot

Link spreadsheet https://docs.google.com/spreadsheets/d/1OTLcaJybrTvlWpwVy03a3cUeN1jPL9ZsYa7m8PddZ-U/edit#gid=0

CodePudding user response：

See if this helps

=countunique(query(A:D, "Select B where A = date '"&TEXT(F2, "yyyy-mm-dd")&"' and C = '"&G2&"' and D matches '^(NEW|)$'", 0))