Are you tired of searching for a COUNTUNIQUEIFS array formula solution in Google Sheets? Look no further! While the dedicated function for this task doesn’t support expanding results, we can still achieve row-wise COUNTUNIQUEIFS in Google Sheets using a combination formula.
Let’s dive right into it with an example. Imagine you have a sheet where you track the sales of five different products. You have columns for the date of sales, the item sold, and the quantity sold. Now, one way to manipulate this data is to find the number of unique items sold per day. In other words, you want to know how many distinct items were sold each day, regardless of the quantity.
Formulas to Get Row-Wise COUNTUNIQUEIFS in Google Sheets
Let’s consider the first two columns (cell range A2:B) of your sheet for this example. Based on this data, we can see that there were two unique items sold on 01/10/2021, three on 03/10/2021, and one each on 04/10/2021 and 05/10/2021. Now, let’s see how we can calculate this.
1. Non-Array Formula
We can achieve this in two simple steps:
- In cell E2, insert the formula
=unique(A2:A). This will return the unique dates from column A, which will act as the criteria. - In cell F2, insert the formula
=COUNTUNIQUEIFS($B$2:$B,$A$2:$A,E2)and drag it down. This formula counts the unique items in column B based on the corresponding date in column A.
The syntax for the COUNTUNIQUEIFS function is COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]).
By following these steps, you can obtain the row-wise COUNTUNIQUEIFS in Google Sheets. However, please note that this is not an array formula, and you cannot use the entire criteria in cell range E2:E5.
2. Array Formula
To achieve a row-wise COUNTUNIQUEIFS array formula in Google Sheets, follow these steps:
-
Make the range E2:F blank/empty, as we will insert a self-expanding array formula in cell E2.
-
Once the above step is 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 serves as an alternative row-wise COUNTUNIQUEIFS array formula in Google Sheets.
Formula Explanation
In this formula, we use two Google Sheets functions: SORTN and QUERY.
SORTN
The SORTN function removes duplicates based on the date and item. If an item is sold multiple times in a day, only the first occurrence is retained.
The syntax for SORTN is SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …]).
QUERY
The QUERY function summarizes the data obtained from SORTN, completing the final formula. It returns a two-column data set containing the date and count of each unique item.
The syntax for QUERY is QUERY(data, query, [headers]).
By using this combination of functions, you can obtain the desired row-wise COUNTUNIQUEIFS array formula in Google Sheets. Take a look at the resulting image below:
Additional Tip – How to Assign Row-Wise COUNTUNIQUEIFS Against Entered Dates
In the non-array formula part, we first specified the dates in column E and then assigned the row-wise COUNTUNIQUEIFS against those dates in column F. However, we used a copy-paste formula in cell F2.
But wait! Can we use an array formula instead?
Absolutely! It’s not as complicated as it sounds. We can use VLOOKUP along with our previous QUERY (which acts as the range in VLOOKUP) to achieve this.
The syntax for VLOOKUP is VLOOKUP(search_key, range, index, [is_sorted]).
To do this:
-
In cell E2, insert the formula
=unique(A2:A). This will give us the unique dates, which will act as the search key in VLOOKUP. -
In cell F2, insert the following 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) ) )
And voila! You now have an array formula in cell F2, achieving the row-wise COUNTUNIQUEIFS against the entered dates.
That’s all for now! I hope you found this article helpful. If you want to explore more amazing Google Sheets tips and tricks, visit Crawlan.com. Happy spreadsheet-ing!
