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.

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 counts TRUE and FALSE 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('MTMxODgwZGJiNzg4NWEyM2M2OGVlZDZjMDQ4ZmVkZWRlZWY4NWRmODUxZWRhYWUwYzFiZmJjM2UyMzcyMzdhNGFkODZiNjIwY2Q1MzMwYmM1YWRmNzY0NDU3NmQ0NjE5YzI1OGUzNjgwZTk3YTExOTMzODBjZDBkYzNlYzk4NWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalV6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MTMxODgwZGJiNzg4NWEyM2M2OGVlZDZjMDQ4ZmVkZWRlZWY4NWRmODUxZWRhYWUwYzFiZmJjM2UyMzcyMzdhNGFkODZiNjIwY2Q1MzMwYmM1YWRmNzY0NDU3NmQ0NjE5YzI1OGUzNjgwZTk3YTExOTMzODBjZDBkYzNlYzk4NWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalV6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MTMxODgwZGJiNzg4NWEyM2M2OGVlZDZjMDQ4ZmVkZWRlZWY4NWRmODUxZWRhYWUwYzFiZmJjM2UyMzcyMzdhNGFkODZiNjIwY2Q1MzMwYmM1YWRmNzY0NDU3NmQ0NjE5YzI1OGUzNjgwZTk3YTExOTMzODBjZDBkYzNlYzk4NWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalV6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MTMxODgwZGJiNzg4NWEyM2M2OGVlZDZjMDQ4ZmVkZWRlZWY4NWRmODUxZWRhYWUwYzFiZmJjM2UyMzcyMzdhNGFkODZiNjIwY2Q1MzMwYmM1YWRmNzY0NDU3NmQ0NjE5YzI1OGUzNjgwZTk3YTExOTMzODBjZDBkYzNlYzk4NWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalV6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub