Formula and Query counting unique values but failing to show zeros

Question

I've been working on a query/formula in Google Sheets with the help of this website.

It is near perfect for our needs yet when there is no data to count, it still shows a 1, where actually it should show a 0.

This is the query

=COUNTUNIQUE(
  QUERY(F2:I,
        "Select G 
          where F='DG' 
            and I is not null 
            and H='Booked' " 
            & IF(C2="<>",,"and I="&C2&"")))

I've created a sample sheet to show the problem here https://docs.google.com/spreadsheets/d/1P0sHIUN12Wpbr6vKJAwsPluj-T2H-NrPpQ1_WWV7lvI/edit?usp=sharing

In the example cell C2 tells the formula in C4 to look for bookings from Decision Week 10 (Column I). There are no bookings in week 10 so cell C4 should show 0, but in fact shows one.

We've designed it so if <> is entered in C2 it shows all unique bookings where column I is not empty.

How do I solve this so a zero is shown? Can anyone help?

Answer 1

The problem stems from 2 facts

• a query returning empty result returns an error, #N/A
• the COUNTUNIQUE() function seems to count #N/A as a valid value type

The fix is to convert the error into a null value, which then will not be counted by COUNTUNIQUE():

=COUNTUNIQUE(
  IFERROR(
    QUERY(F2:I,
          "Select G 
            where F='DG' 
              and I is not null 
              and H='Booked' " 
              & IF(C2="<>",,"and I="&C2&"")),
    ""))