I'm having difficultly with Google Sheets counting unique rows, where other criteria in the formula must also be met.
I've created a simple Google Sheet to highlight the problem I need solving. https://docs.google.com/spreadsheets/d/1P0sHIUN12Wpbr6vKJAwsPluj-T2H-NrPpQ1_WWV7lvI/edit?usp=sharing
I need the formula in cell C5 to only count unique values in row G, where the criteria is met in rows F & H (the SP is “DG” and the likelihood is “Booked”). I cannot work out how to make this only count unique values. The formula I currently have only counts any cells that are not blank in row G, it does not look for unique values but needs to.
The working formula should give the following answers:
C3 = <> C5 should equal 3
C3 = 1 C5 should equal 1
C3 = 2 C5 should equal 2
C3 = 3 C5 should equal 1
Please can someone help?
EDIT The formulas I've already tried won't make sense with the example in the link but to show what I have attempted
=COUNTIF(UNIQUE(QUERY(AA:AM,"Select AC where AA = 'DG' and AL = 'Booked' and AM = '" & I2 & "'")),"<>")
It returned a 1 regardless of how many it should have returned.
=COUNTUNIQUE(QUERY(F2:I21,"Select G where F='DG' and H='Booked' "&IF(ISBLANK(C3),,"and I="&C3&"")))