COUNTUNIQUE function
PRO
The COUNTUNIQUE
function in Jspreadsheet Formulas Pro is a useful tool that helps you determine the number of unique entries within a specific range of cells. This means it will only count each distinct value once, regardless of how many times it appears in the selected cells. For instance, if you have a list of names with some duplicates, COUNTUNIQUE
will provide you with the total number of different names. It's an efficient way to identify the diversity of data in your spreadsheet.
Documentation
Returns the number of unique values in a range of cells.
Category
Math and statistics
Syntax
COUNTUNIQUE(value1, [value2], ...)
Parameter | Description |
---|---|
value |
The first value or range to count unique values from. |
value2,... |
Optional. Additional values or ranges to count unique values from. |
Behavior
The COUNTUNIQUE
function is used to count the number of unique values in a list or range of cells.
-
Empty cells:
COUNTUNIQUE
ignores empty cells in the range or list of values. -
Text: The function treats each unique text string as a unique value.
-
Booleans:
COUNTUNIQUE
countsTRUE
andFALSE
as unique values. -
Errors: If any cell in the range or list of values contains an error,
COUNTUNIQUE
will return an error. -
Numbers: Each unique number is counted as a unique value.
-
Date and Time:
COUNTUNIQUE
treats each unique date or time as a unique value.
Common Errors
Error | Description |
---|---|
#VALUE! | This error occurs if the function is not properly formatted. |
#REF! | This error occurs if the range or list of values refers to a cell that is not valid. |
Best practices
- When using the
COUNTUNIQUE
function, ensure that the range or list of values does not contain any errors to avoid the#N/A
error.- The function does not count empty cells. If you want to include empty cells in your count, you will have to handle this separately.
- To avoid getting the
#REF!
error, always ensure that your range refers to valid cells.
Usage
A few examples using the COUNTUNIQUE function.
| Formula | Description | Result |
|----------------------------------------|--------------------------------------------------------------------|--------|
| COUNTUNIQUE(1, 2, 3, 4, 5) | Counts 5 distinct numeric values | 5 |
| COUNTUNIQUE(A2:A6) | Counts how many unique values are in range A2 to A6 | Varies |
| COUNTUNIQUE(B2:B6, D2:D6) | Counts unique values across two different ranges | Varies |
| COUNTUNIQUE("a", "A", "A", "a") | Case-sensitive: counts both "a" and "A" separately | 2 |
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Product",
"Sales Rep",
"Region"
],
[
"Laptop",
"John",
"North"
],
[
"Phone",
"Sarah",
"South"
],
[
"Laptop",
"Mike",
"North"
],
[
"Tablet",
"John",
"West"
],
[
"Phone",
"Sarah",
"North"
],
[
"=COUNTUNIQUE(A2:A6)",
"=COUNTUNIQUE(B2:B6)",
"=COUNTUNIQUE(C2:C6)"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Product",
"Sales Rep",
"Region"
],
[
"Laptop",
"John",
"North"
],
[
"Phone",
"Sarah",
"South"
],
[
"Laptop",
"Mike",
"North"
],
[
"Tablet",
"John",
"West"
],
[
"Phone",
"Sarah",
"North"
],
[
"=COUNTUNIQUE(A2:A6)",
"=COUNTUNIQUE(B2:B6)",
"=COUNTUNIQUE(C2:C6)"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Product",
"Sales Rep",
"Region"
],
[
"Laptop",
"John",
"North"
],
[
"Phone",
"Sarah",
"South"
],
[
"Laptop",
"Mike",
"North"
],
[
"Tablet",
"John",
"West"
],
[
"Phone",
"Sarah",
"North"
],
[
"=COUNTUNIQUE(A2:A6)",
"=COUNTUNIQUE(B2:B6)",
"=COUNTUNIQUE(C2:C6)"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZGM1Zjk1M2Y3ZTM1Y2Y0NDg2MzM1YjQwY2JjYTNlNDc5ZjJkMDNiYmRjNTRmYjc2NzhkNWEyZjcwZmU4OWRlNmMyZjdlNDZkYjU3NTY0NGE4YzRkMjgwNTBiY2U0OWJlZTFjMzUyYThkY2QwNDdjNTA3MDAxNDQ0M2FiNzg4NGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Product",
"Sales Rep",
"Region"
],
[
"Laptop",
"John",
"North"
],
[
"Phone",
"Sarah",
"South"
],
[
"Laptop",
"Mike",
"North"
],
[
"Tablet",
"John",
"West"
],
[
"Phone",
"Sarah",
"North"
],
[
"=COUNTUNIQUE(A2:A6)",
"=COUNTUNIQUE(B2:B6)",
"=COUNTUNIQUE(C2:C6)"
]
]
}]
});
}
}