Are you looking for a COUNTUNIQUEIFS array formula solution in Google Sheets?

Unfortunately, the dedicated function for this won’t support expanding results. But, with the help of a combination formula, we can get row-wise COUNTUNIQUEIFS in Google Sheets.

Let me clarify it further with an example.

Assume you have five products for sale, and you are tracking the sales in a Sheet like date of sales, item sold, and sold quantity.

You can manipulate such data in several ways in Google Sheets. One such manipulation is to find the number of unique items sold per day.

I mean, out of five products, how many unique items are sold per day.

What does it mean?

If an item is dispatched more than once in a day, only count it as 1.

Formulas to Get Row-Wise COUNTUNIQUEIFS in Google Sheets

In the below example, we will consider the first two columns (cell range A2:B) for the test.

As per this data, there are two unique items sold on 01/10/2021, three on 03/10/2021, and one each on 04/10/2021 and 05/10/2021.

How to perform this calculation?

1. Non-Array Formula

We can do this in two steps.

1. In cell E2 insert =unique(A2:A) to return the UNIQUE dates. It acts as the criteria.
2. In cell F2 insert =COUNTUNIQUEIFS($B$2:$B,$A$2:$A,E2) and drag down.

Syntax:- COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Thus we can get row-wise COUNTUNIQUEIFS in Google Sheets. But it’s not an array formula.

We can’t use the whole criteria in cell range E2:E5 as below.

=ArrayFormula(COUNTUNIQUEIFS($B$2:$B,$A$2:$A,E2:E5))

It won’t expand the COUNTUNIQUEIFS result into the adjoining rows.

If you want a row-wise COUNTUNIQUEIFS array formula in Google Sheets, please follow the below steps.

2. Array Formula

Please make the range E2:F blank/empty because we want to insert a self-expanding array formula in cell E2.

Once done, insert the following QUERY and SORTN combination formula in cell F2.

=QUERY(
     sortn(A2:B,9^9,2,B2:B&A2:A,1),
     "Select Col1,count(Col2) where Col2 <>'' 
     group by Col1 label Count(Col2)''",0
)

The above formula works as an alternative row-wise COUNTUNIQUEIFS array formula in Google Sheets.

Formula Explanation

We have used two Google Sheets functions in the combination. They are SORTN and QUERY.

SORTN – removes duplicates based on date and item. If an item is sold multiple times in a day, the first occurrence is retained.

Syntax:- SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])

Related:- SORTN Tie Modes in Google Sheets – The Four Tiebreakers.

The role of QUERY is to summarize this data, and that’s the final formula.

Syntax:- QUERY(data, query, [headers])

The formula returns two-column data that contains a date column and a count column.

Additional Tip – How to Assign Row-Wise COUNTUNIQUEIFS Against Entered Dates

In the non-array formula part, we have first specified the dates in column E and then assigned the row-wise COUNTUNIQUEIFS against those dates in column F.

But, there, we have used a copy-paste formula in cell F2.

Can we use an array formula in cell F2 instead?

Yep! It’s not that much complicated.

We can use VLOOKUP with our earlier Query (acts as the range in Vlookup) as below.

Syntax:- VLOOKUP(search_key, range, index, [is_sorted])

Prerequisite:- In cell E2 insert =unique(A2:A) (acts as the search_key in Vlookup).

Then in cell F2, insert the below row-wise COUNTUNIQUEIFS array formula.

=ArrayFormula(
     IFNA(
        vlookup(
           E2:E,
           query(
              sortn(A2:B,9^9,2,B2:B&A2:A,1),
              "Select Col1,count(Col2) where Col2 <>'' 
              group by Col1",0
           )
        ,2,0)
     )
)

That’s all. Thanks for the stay. Enjoy!

Related Resources

• How to Use COUNTIF with UNIQUE in Google Sheets.
• Count Unique Values in Visible Rows in Google Sheets.
• Count Unique Dates in a Date Range – 5 Formula Options in Google Sheets.