How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Saturday

Row-Wise COUNTUNIQUEIFS in Google Sheets (Array Formula)

Posted by   on

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.


Row-Wise COUNTUNIQUEIFS in Google Sheets - Non-Array

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.


SORTN Step

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.


Row-Wise COUNTUNIQUEIFS in Google Sheets - Array Formula

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

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!