Skip to content
Browse all functions

Sumif Spreadsheet Function for an Array

spreadsheet/sumif

sumif (Sumif Spreadsheet Function for an Array) is a 1001fx API function in the group Spreadsheet: POST https://api.1001fx.com/spreadsheet/sumif, 2 credits per call. It works as a ready-made module in Make and n8n or as a plain HTTP call.

2 credits per callHTTP APIn8nMake

Implements a sumif function similar to GSheet and Excel functions. Sum elements of an array by a given criterion.

Try it in the 1001fx app

Run sumif in your browser with your API key – fill the form, click Run and get the answer or the file.

sumif in the 1001fx app: the form with example values
Sumif Spreadsheet Function for an Array in the 1001fx app

Endpoint

POSThttps://api.1001fx.com/spreadsheet/sumif
Header
X-API-KEY: YOURSECRETAPIKEY How to
Content type
application/json

Parameters

  • dataarray<object>required

    Array of objects with at least two properties e.g. [{name,score}] each.

  • queryFieldstringrequired

    Name of the field in data to look for critera. Case sensitive

    Example:
    name
  • operatorenumoptional

    Comparison operator. Defaults to eq

    Values:
    eq
    gt
    ge
    lt
    le
    ne
    Default:
    eq
    Example:
    eq
  • criteriastringrequired

    Criteria to look for must be number or string

    Example:
    Bob
  • sumFieldstringrequired

    Name of the field in data to sum up. Case sensitive.

    Example:
    score
  • ignoreCasebooleanoptional

    Sets case sensitivity for criteria operation. Defaults to true

    Default:
    true
    Example:
    false

Examples

Sum

Result is 18.5

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1
    },
    {
      "name": "Bob",
      "score": 2
    },
    {
      "name": "Charlie",
      "score": 4
    },
    {
      "name": "Dave",
      "score": 8
    },
    {
      "name": "Bob",
      "score": 16.5
    }
  ],
  "queryField": "name",
  "sumField": "score",
  "operator": "eq",
  "criteria": "Bob",
  "ignoreCase": false
}

Output · HTTP 200

Response
{
  "result": 18.5
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/sumif' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1},{"name":"Bob","score":2},{"name":"Charlie","score":4},{"name":"Dave","score":8},{"name":"Bob","score":16.5}],"queryField":"name","sumField":"score","operator":"eq","criteria":"Bob","ignoreCase":false}'

Operator ==

Result is sum of 33 based on default operator "=="

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1
    },
    {
      "name": "Bob",
      "score": 2
    },
    {
      "name": "Charlie",
      "score": 4
    },
    {
      "name": "Dave",
      "score": 8
    },
    {
      "name": "Bob",
      "score": 16
    },
    {
      "name": "Alice",
      "score": 32
    }
  ],
  "queryField": "name",
  "sumField": "score",
  "criteria": "Alice",
  "ignoreCase": false
}

Output · HTTP 200

Response
{
  "result": 33
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/sumif' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1},{"name":"Bob","score":2},{"name":"Charlie","score":4},{"name":"Dave","score":8},{"name":"Bob","score":16},{"name":"Alice","score":32}],"queryField":"name","sumField":"score","criteria":"Alice","ignoreCase":false}'

Operator !=

Result is sum of 13 on != operator

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1
    },
    {
      "name": "Bob",
      "score": 2
    },
    {
      "name": "Charlie",
      "score": 4
    },
    {
      "name": "Dave",
      "score": 8
    },
    {
      "name": "Bob",
      "score": 16
    }
  ],
  "queryField": "name",
  "sumField": "score",
  "operator": "ne",
  "criteria": "Bob"
}

Output · HTTP 200

Response
{
  "result": 13
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/sumif' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1},{"name":"Bob","score":2},{"name":"Charlie","score":4},{"name":"Dave","score":8},{"name":"Bob","score":16}],"queryField":"name","sumField":"score","operator":"ne","criteria":"Bob"}'

Operator >=

Result is sum of 28 based on >= comparison

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1
    },
    {
      "name": "Bob",
      "score": 2
    },
    {
      "name": "Charlie",
      "score": 4
    },
    {
      "name": "Dave",
      "score": 8
    },
    {
      "name": "Eve",
      "score": 16
    }
  ],
  "queryField": "name",
  "sumField": "score",
  "operator": "ge",
  "criteria": "C"
}

Output · HTTP 200

Response
{
  "result": 28
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/sumif' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1},{"name":"Bob","score":2},{"name":"Charlie","score":4},{"name":"Dave","score":8},{"name":"Eve","score":16}],"queryField":"name","sumField":"score","operator":"ge","criteria":"C"}'

ignoreCase true

Result is sum of 7 based on ignoreCase is true

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1
    },
    {
      "name": "alice",
      "score": 2
    },
    {
      "name": "Alice",
      "score": 4
    },
    {
      "name": "Dave",
      "score": 8
    },
    {
      "name": "Eve",
      "score": 16
    }
  ],
  "queryField": "name",
  "sumField": "score",
  "operator": "eq",
  "criteria": "Alice",
  "ignoreCase": true
}

Output · HTTP 200

Response
{
  "result": 7
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/sumif' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1},{"name":"alice","score":2},{"name":"Alice","score":4},{"name":"Dave","score":8},{"name":"Eve","score":16}],"queryField":"name","sumField":"score","operator":"eq","criteria":"Alice","ignoreCase":true}'

ignoreCase false

Result is sum of 5 based on ignoreCase is false

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1
    },
    {
      "name": "alice",
      "score": 2
    },
    {
      "name": "Alice",
      "score": 4
    },
    {
      "name": "Dave",
      "score": 8
    },
    {
      "name": "Eve",
      "score": 16
    }
  ],
  "queryField": "name",
  "sumField": "score",
  "operator": "eq",
  "criteria": "Alice",
  "ignoreCase": false
}

Output · HTTP 200

Response
{
  "result": 5
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/sumif' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1},{"name":"alice","score":2},{"name":"Alice","score":4},{"name":"Dave","score":8},{"name":"Eve","score":16}],"queryField":"name","sumField":"score","operator":"eq","criteria":"Alice","ignoreCase":false}'

Try sumif now

Run it in your browser or create a free account and get 100 credits every month.

Changelog

  • 1.0.004.01.2023created
Ask 1001fx