Search code examples
arraysgoogle-sheetsfilteruniquegoogle-sheets-formula

Count rows which meet criteria, ignoring rows with same non-unique value (I.E all awards before a certain date in a unique country)

Below is the sheet template I am working on.

Sheet to find function for

and here is a link to a copy of it: https://docs.google.com/spreadsheets/d/1qfbDihg0q3XTWCEdHUwT7RLjTerIeK0z-JLuihkmTp4/edit?usp=sharing

I would like to be able to count each row that meets a set of criteria, for example I would like to know each row that received an award in a unique country after 2015

I have tried a formula like this (subRec is the name of the sheet shown above):

=COUNTIFS(Arrayformula(IF(subRec!E2:E=FALSE,FALSE,true)),True,ARRAYFORMULA(IF(subRec!C2:C<DATE(2015,1,1),False,True)),true,ARRAYFORMULA(if((COUNTIF(subRec!F2:F,subRec!F2:F))>1,False,True)),true)

However, this results in a reduction in the count if I were to add another row which had a non-unique country, since it filters out ALL non-uniques rather than counting only the first successful one.

Ideally, the formula should:

  1. check each row against an arbitrary number of criteria and count that row if it meets all of them

  2. where there are uniques for columns like Country or Salon, find the first row which meets all criteria, add it to the count and ignore the rest that have that same non-unique value

Solution

  • try like this:

    =COUNTA(IFERROR(UNIQUE(FILTER(subRec!F2:F, 
 subRec!E2:E=TRUE, 
 subRec!C2:C>DATE(2015, 1, 1)))))

    0

    0