How to Get the Cumulative Count of Distinct Values in Google Sheets

Knowing how to calculate the cumulative count of distinct values can be helpful in many ways. For example, you can use it to track how many unique customers have made a purchase over time or count how many different products have been sold cumulatively.

In Google Sheets, you can use the SCAN function to get the cumulative count of distinct values.

Generic Formula

=ArrayFormula(IF(range="",,SCAN(0, range, LAMBDA(acc, values, IF(COUNTIF(A2:values, values)=1, acc+1, acc)))))

When using this formula, replace range with the column range that you want to analyze for the running count of distinct values.

As a side note, the last value in the cumulative count of distinct values will be equal to the total count of unique values in the range.

Example: Cumulative Count of Distinct Values in a Column

Assume you have the following names in A2:A in Google Sheets:

Jeri
Valerie
Jeri
Charlie
Bob
Charlie
Stacey
Stacey

In cell B2, enter the following formula to get the cumulative count of distinct names:

=ArrayFormula(IF(A2:A="",,SCAN(0, A2:A, LAMBDA(acc, values, IF(COUNTIF(A2:values, values)=1, acc+1, acc)))))

Result:

Example of a running count of distinct names in a column in Google Sheets

Formula Explanation

  • COUNTIF(A2:values, values) – Returns the running count of occurrences of the current row’s value. This is equivalent to entering =COUNTIF($A$2:A2, A2) in B2 and dragging it down.
  • In this COUNTIF, values represents the current value/reference.
  • IF(COUNTIF(A2:values, values) = 1, acc + 1, acc) – If the count of values is 1, the formula adds 1 to the accumulator (acc); otherwise, it retains the previous count.
  • The SCAN function iterates this logic for each value in the array, returning the running count of distinct values in Google Sheets.
  • The IF statement at the beginning ensures that empty rows do not display any result.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.