Sum, Count, Average, Max, Min Values by Color in Spreadsheet/Excel

Overview

The API can perform data calculations based on cell colour. It can sum, count, average, and also find the maximum and minimum values in an Excel spreadsheet according to the fill or font colour of the cells.

Calculate Operation Description
Count Determine the number of cells with the same colour.
Sum Calculate the total value of cells with the same colour.
Max Value Identify the highest value among cells with the same colour.
Min Value Find the lowest value among cells with the same colour.
Average Value Compute the mean value of cells with the same colour.

Authentication

To call the Aggregate by Colour endpoint you must obtain an OAuth 2.0 access token.

  1. Register an application in the Aspose Cloud Dashboard to receive a Client Id and Client Secret.

  2. Request a token

    POST https://api.aspose.cloud/connect/token
Content-Type: application/x-www-form-urlencoded

grant_type=client_credentials&client_id=<YOUR_CLIENT_ID>&client_secret=<YOUR_CLIENT_SECRET>

    The response contains an access_token.

  3. Include the token in every API request

    Authorization: Bearer <access_token>

The token is valid for one hour; refresh it by repeating step 2.

Web API

PUT https://api.aspose.cloud/v4.0/cells/calculate/aggregate/color

Request Parameters

Parameter Name Type Location Description
Spreadsheet File FormData The Excel workbook to process.
Worksheet String Query Name of the worksheet that contains the range.
Range String Query A‑1 style range (e.g., A1:B10).
Operation String Query Calculation method – Sum, Count, Average, Min, or Max.
ColorPosition String Query Determines which colour to evaluate – Background, Font.
Region String Query The spreadsheet region setting.
Password String Query Password for opening a protected workbook (optional).

Enumerations

  • ColorPosition
    Value Meaning
    Background Use the cell’s fill colour.
    Font Use the cell’s font colour.

Response

The schema below describes the response object. A concrete example follows the schema.

{
  "Name": "AggregateResultByColorResponse",
  "Type": "Class",
  "ParentName": "CellsCloudResponse",
  "IsAbstract": false,
  "Properties": [
    {
      "Name": "AggregateResults",
      "DataType": {
        "Identifier": "Array",
        "Reference": "AggregateResultByColor",
        "ElementDataType": {
          "Reference": "AggregateResultByColor"
        }
      }
    },
    {
      "Name": "Code",
      "DataType": { "Identifier": "Integer" }
    },
    {
      "Name": "Status",
      "DataType": { "Identifier": "String" }
    }
  ]
}

Example response (real‑world values)

{
  "Code": 200,
  "Status": "OK",
  "AggregateResults": [
    {
      "Color": "#FF0000",
      "Count": 12,
      "Sum": 345.67,
      "Average": 28.8,
      "Min": 5.0,
      "Max": 80.0
    },
    {
      "Color": "#00FF00",
      "Count": 7,
      "Sum": 210.0,
      "Average": 30.0,
      "Min": 10.0,
      "Max": 50.0
    }
  ]
}

Error Codes

  • 400 Bad Request – Invalid Aspose.Cells Cloud API URI.
  • 401 Unauthorized – Invalid or missing access token, or incorrect client credentials.
  • 404 Not Found – The specified spreadsheet cannot be accessed.
  • 500 Server Error – The service encountered an unexpected condition while processing the request.

Where should we use the Aggregate by Colour API?

In a spreadsheet, data from different categories is often colour‑coded. This API enables you to sum, count, average, or find the minimum and maximum values for each colour group, simplifying colour‑based data analysis.

Why should you use the Aggregate by Colour API?

The API provides a fast, reliable way to perform colour‑based calculations without writing custom parsing logic. It integrates seamlessly with Aspose.Cells Cloud SDKs, allowing developers to implement colour aggregation with just a few lines of code.

How to Use the Aggregate by Colour API with SDKs

Aggregate by Colour API Specification

The Aggregate by Colour API Specification defines a publicly accessible programming interface and allows you to carry out REST interactions directly from a web browser.

Use Aspose.Cells Cloud SDKs

Using the SDK is the fastest way to develop, as it abstracts away the low‑level details, allowing you to aggregate calculations by cell colour with just a short piece of code.
Please check out the GitHub repository for a complete list of Aspose.Cells Cloud SDKs.

The following code examples demonstrate how to make calls to Aspose.Cells web services using various SDKs: