How to Sum Story Points in Jira (Free, with Google Sheets)

Jira’s dashboard gadgets count work items but don’t add up a number field, so the simplest free way to sum story points by sprint, epic, assignee or status is to import the work items into Google Sheets and total them with SUMIFS, QUERY or a pivot table. Atlassian’s free Jira Cloud for Sheets add-on or a CSV export gets the numbers in, and a short Apps Script does it on a schedule, including on sites that keep points in two different fields. The same formulas work for any number field, such as cost or test estimates.

Jira does total story points in a few places, and one of them may be enough, so that comes first. Then the import, the script, formulas checked against a small example, and the mistakes that make totals wrong. Facts are as of October 2026.

What Jira adds up natively

Where	What it adds up	Limits
Velocity chart	Committed and completed estimate per sprint, in story points, original time or any numeric custom field	Company-managed scrum; only items in the board’s filter; subtask estimates excluded
Velocity report	Story points or item count per sprint	Team-managed spaces; subtask estimates excluded
Sprint report	Completed and not completed story points for one sprint	Company-managed scrum; subtask estimates excluded
Epic report	Complete, incomplete and unestimated work in one epic	Company-managed scrum; one epic at a time
Backlog	Points per sprint, split into To Do, In Progress and Done	Follows the board’s column mapping (Atlassian KB)
Space insights	Charts with a Total story points metric, grouped by sprint, parent, assignee, team or status category	One space per page; a second breakdown and some metrics need Premium or Enterprise; 13 months of history on Standard
Plans	Child estimates rolled up to parents	Premium and Enterprise
Dashboard gadgets	Nothing: Pie Chart, Work Item Statistics and Two Dimensional Filter Statistics count work items	Sums requested in JRACLOUD-62109 since 2011; 854 votes, still “Gathering Interest”

Space insights is the one most people haven’t tried. If a space’s Reports page opens on a Team overview dashboard, it has them. Atlassian’s metric list for these charts includes “Total story points”, and the grouping options include Sprint, Parent and Work item assignee. Atlassian says some metrics are Premium and Enterprise only without listing which, so check on your plan before promising leadership a chart.

Three other routes come up often:

• JQL has no SUM or GROUP BY. It can scope a total but can’t produce one.
• Automation can put one number on a dashboard: a scheduled rule runs Lookup work items with your JQL, sets a number field on a single “report” work item to {{lookupIssues.Story Points.sum}} (.sum is a list smart value function), and a Filter Results gadget shows that work item. The lookup only uses the first 100 work items (automation limits).
• Insights, formerly Atlassian Analytics, now comes with paid Standard, Premium and Enterprise plans instead of Enterprise only. The full chart builder and SQL mode stay on Enterprise, and its Rovo-powered features need Rovo turned on and use Rovo credits (Atlassian).

A spreadsheet is worth it when you need a number field other than story points outside the velocity chart, several boards or spaces in one table, done against remaining for every epic at once, or a weekly history Jira doesn’t keep.

Get the number field into Google Sheets

Our guide to exporting Jira to Google Sheets compares the import methods. Whichever you use, scope it with JQL like this:

project = ABC AND issuetype in standardIssueTypes() AND issuetype != Epic AND (statusCategory != Done OR statusCategoryChangedDate >= -180d)

• issuetype in standardIssueTypes() leaves out subtasks, so a story and its subtasks aren’t counted twice. Atlassian’s JQL functions page now writes it as workType in standardWorkTypes().
• issuetype != Epic leaves out epics, which can carry their own estimate. Use your site’s name for that level if it was renamed.
• The last part keeps everything open plus work finished in the last 180 days. Widen it if your epics run longer.

With the add-on, open Extensions > Jira Cloud for Sheets > Open > Get data from Jira, paste the JQL, and choose the columns Key, Status Category, Assignee, Sprint, Parent, Story Points and Summary, plus Story point estimate if you have team-managed spaces. Name the tab Jira and set a daily schedule in the sidebar. For a one-off total, a CSV export of a search with the same columns works too. The formulas below use the script’s column order, so change the letters to match yours.

Find the story points field and its ID

Company-managed spaces use Story Points and team-managed spaces use Story point estimate, two separate fields according to Atlassian’s estimation docs and a KB article. A suggestion to merge them, JRACLOUD-94950, is “Under Consideration” with 179 votes.

Field IDs differ by site too: Atlassian’s own examples show Story Points as customfield_10021 on one site and customfield_10106 on another. Atlassian’s KB on custom field IDs gives these ways to find yours:

• In search results, add Story Points as a column, sort by it, and switch to JQL. The ORDER BY clause shows the ID, such as cf[10021]. No admin rights needed.
• While signed in, open https://your-site.atlassian.net/rest/api/3/field and search the page for “Story”. No admin rights needed.
• Jira admins can choose View field information next to the field in the custom fields list; the URL ends in customFieldId=10021.

The script below finds fields by name, so you only need the ID for JQL such as cf[10021] is EMPTY.

The Apps Script

The script reads Jira’s field list, finds the fields named in POINT_FIELDS, then pages through /rest/api/3/search/jql with nextPageToken, writing one row per work item.

1. Create an API token at id.atlassian.com/manage-profile/security/api-tokens with Create API token (not the scoped option, so the script can call your site URL). New tokens expire after at most a year.
2. In your sheet, open Extensions > Apps Script. Under Project Settings, add script properties JIRA_SITE (like your-site.atlassian.net), JIRA_EMAIL and JIRA_TOKEN. Anyone who can edit the sheet can read them.
3. Paste the script, change the JQL, and run importPoints once to authorize it.
4. Under Triggers, add a daily time-driven trigger for importPoints.

// Field names to add up, in order of preference. Company-managed spaces
// usually use "Story Points", team-managed spaces "Story point estimate".
const POINT_FIELDS = ['Story Points', 'Story point estimate'];
const JQL = 'project = ABC AND issuetype in standardIssueTypes() AND issuetype != Epic ' +
  'AND (statusCategory != Done OR statusCategoryChangedDate >= -180d)';

function importPoints() {
  const props = PropertiesService.getScriptProperties();
  const site = props.getProperty('JIRA_SITE');
  const auth = 'Basic ' + Utilities.base64Encode(
    props.getProperty('JIRA_EMAIL') + ':' + props.getProperty('JIRA_TOKEN'));
  const all = callJira(site, auth, '/rest/api/3/field');
  const pointIds = [];
  POINT_FIELDS.forEach(function (name) {
    all.forEach(function (f) {
      if (f.name.toLowerCase() === name.toLowerCase()) pointIds.push(f.id);
    });
  });
  const sprint = all.filter(function (f) { return f.name === 'Sprint'; }).map(function (f) { return f.id; })[0];
  const fields = ['summary', 'status', 'assignee', 'parent', 'statuscategorychangedate', sprint]
    .concat(pointIds).filter(Boolean);

  const rows = [['Key', 'Status category', 'Assignee', 'Sprint', 'Parent', 'Points', 'Done date', 'Summary']];
  let token = null;
  do {
    let path = '/rest/api/3/search/jql?maxResults=100&fields=' + fields.join(',') +
      '&jql=' + encodeURIComponent(JQL);
    if (token) path += '&nextPageToken=' + encodeURIComponent(token);
    const data = callJira(site, auth, path);
    data.issues.forEach(function (i) {
      const f = i.fields;
      const points = pointIds.map(function (id) { return f[id]; })
        .filter(function (v) { return typeof v === 'number'; });
      const done = f.status.statusCategory.key === 'done';
      rows.push([i.key, f.status.statusCategory.name,
        f.assignee ? f.assignee.displayName : 'Unassigned',
        currentSprint(f[sprint]),
        f.parent ? f.parent.key + ' ' + f.parent.fields.summary : '',
        points.length ? points[0] : '',
        done && f.statuscategorychangedate ? new Date(f.statuscategorychangedate) : '',
        f.summary]);
    });
    token = data.nextPageToken;
  } while (token);

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Jira') || ss.insertSheet('Jira');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  sheet.getRange('J1').setValue(new Date());
}

// A carried-over item lists every sprint it was in. Keep the open one,
// or else the one that closed last.
function currentSprint(sprints) {
  if (!sprints || !sprints.length) return '';
  const open = sprints.filter(function (s) { return s.state !== 'closed'; });
  if (open.length) return open[0].name;
  return sprints.slice().sort(function (a, b) {
    return String(b.completeDate).localeCompare(String(a.completeDate));
  })[0].name;
}

function callJira(site, auth, path) {
  const res = UrlFetchApp.fetch('https://' + site + path, {
    headers: { Authorization: auth, Accept: 'application/json' },
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) {
    throw new Error('Jira returned ' + res.getResponseCode() + ': ' +
      res.getContentText().slice(0, 300));
  }
  return JSON.parse(res.getContentText());
}

• Points is the first field in POINT_FIELDS with a value, so one column covers both space types. The order works as a rule: ['Test Estimation', 'Story Points'] gives each item its test estimate if it has one and its story points otherwise.
• Sprint is the open sprint, or for finished work the sprint that closed last, so carried-over items count once.
• Done date is when a done item last changed status category, which also works in spaces that don’t set a resolution.
• J1 holds the time of the last successful run. A failed run stops before it and shows up in Apps Script’s trigger failure emails. Each run must finish within Google’s 6-minute limit (quotas).

Sum it in Sheets

Every formula below was checked against this small Jira tab (Summary, in column H, is left out):

Row	A: Key	B: Status category	C: Assignee	D: Sprint	E: Parent	F: Points	G: Done date
2	ABC-1	Done	Dana	Sprint 41	ABC-100 Accounts	5	2026-10-08
3	ABC-2	Done	Lee	Sprint 42	ABC-100 Accounts	3	2026-10-21
4	ABC-3	In Progress	Dana	Sprint 42	ABC-200 Compliance	8
5	ABC-4	To Do	Lee	Sprint 42	ABC-200 Compliance	2
6	ABC-5	To Do	Unassigned	Sprint 43	ABC-100 Accounts
7	ABC-6	To Do	Unassigned		ABC-200 Compliance	13

Keep every formula on other tabs. Each refresh rewrites the Jira tab, and anything typed there is lost or lands next to the wrong row.

Done, remaining and unestimated

On a Summary tab, with labels in column A:

B2 (done):         =SUMIF(Jira!B2:B, "Done", Jira!F2:F)
B3 (in progress):  =SUMIF(Jira!B2:B, "In Progress", Jira!F2:F)
B4 (to do):        =SUMIF(Jira!B2:B, "To Do", Jira!F2:F)
B5 (unestimated):  =COUNTIFS(Jira!A2:A, "<>", Jira!F2:F, "")
B6 (refreshed):    =Jira!J1

The example gives 8 done, 8 in progress, 15 to do (31 in total) and 1 unestimated item. Blank cells add nothing, so without the unestimated count, 31 looks like the whole scope.

Remaining points in one sprint, and in the backlog (no sprint), with SUMIFS:

=SUMIFS(Jira!F2:F, Jira!D2:D, "Sprint 42", Jira!B2:B, "<>Done")
=SUMIFS(Jira!F2:F, Jira!A2:A, "<>", Jira!D2:D, "", Jira!B2:B, "<>Done")

These return 10 (ABC-3 and ABC-4) and 13 (ABC-6).

By sprint

One QUERY gives points per sprint, split by status category:

=QUERY(Jira!A1:G, "select D, sum(F) where A is not null group by D pivot B", 1)

Sprint	Done	In Progress	To Do
(blank)			13
Sprint 41	5
Sprint 42	3	8	2
Sprint 43

The blank row is the backlog, and Sprint 43 is empty because its only item has no estimate. For committed against completed points across many sprints, use the velocity chart: it remembers what each sprint started with, which a snapshot can’t.

By epic

On an Epics tab, put =UNIQUE(FILTER(Jira!E2:E, Jira!E2:E <> "")) in A2, then these in row 2 and fill down:

B2 (total):        =SUMIFS(Jira!F:F, Jira!E:E, A2)
C2 (done):         =SUMIFS(Jira!F:F, Jira!E:E, A2, Jira!B:B, "Done")
D2 (% done):       =IF(B2 = 0, "", C2 / B2)
E2 (unestimated):  =COUNTIFS(Jira!E:E, A2, Jira!F:F, "")

ABC-100 Accounts shows 8 total, 8 done, 100% and 1 unestimated item; ABC-200 Compliance shows 23, 0 and 0%. Keep the unestimated column next to the percentage, or “100% done” reaches leadership while ABC-5 has no estimate.

By assignee

=QUERY(Jira!A1:G, "select C, sum(F) where B <> 'Done' and A is not null group by C order by sum(F) desc label sum(F) 'Open points'", 1)

This returns open points per person, largest first: Unassigned 13, Dana 8, Lee 2.

If you’d rather click than type, a pivot table does the same: select columns A to H of the Jira tab, choose Insert > Pivot table, add Parent or Sprint under Rows, Status category under Columns and Points under Values, summarized by SUM. Filter Key by condition “Is not empty” to drop the blank row.

Any number field

The formulas don’t care what the number means. For cost fields, import Internal cost and External cost as, say, columns F and I, and add a second sum: select B, sum(F), sum(I) where A is not null group by B. To count a test estimate instead of story points wherever one exists, with Test Estimation in column I:

=SUMIFS(Jira!F2:F, Jira!I2:I, "") + SUM(Jira!I2:I)

That’s story points from items without a test estimate, plus every test estimate.

Track totals over time

A refresh only shows today. Two additions give you a trend.

Points completed per week. On a Trend tab, type the first Monday in A2, put =A2 + 7 in A3 and fill down, then fill these down from row 2:

B2 (done that week):  =SUMIFS(Jira!F:F, Jira!G:G, ">=" & A2, Jira!G:G, "<" & (A2 + 7))
C2 (running total):   =SUM(B$2:B2)

Starting at 2026-10-05, the example gives 5, 0 and 3 points, with running totals of 5, 5 and 8. It covers work finished inside the import’s 180-day window.

A weekly snapshot. Jira doesn’t keep last week’s totals, so copy them. Add this to the same Apps Script project, create a History tab, and add a weekly time-driven trigger for Friday afternoon:

function snapshotTotals() {
  const ss = SpreadsheetApp.getActive();
  const totals = ss.getRange('Summary!B2:B5').getValues().map(function (r) { return r[0]; });
  ss.getSheetByName('History').appendRow([new Date()].concat(totals));
}

Each row holds the date, done, in progress, to do and unestimated. A stacked area chart of the three point columns is a burnup: if the top of the stack climbs, scope was added or re-estimated, which a single “done versus remaining” number hides. Our weekly Jira status report uses the same history pattern for counts.

Mistakes that make totals wrong

• Unestimated work counts as zero. Show the unestimated count next to every total; "Story Points" is EMPTY finds those items in Jira.
• Subtasks counted with their parents. Jira’s velocity and sprint reports ignore subtask estimates. If your team estimates both levels, keep subtasks out as above, or the sheet won’t match Jira.
• Epics with their own estimate. By default, story points can be set on stories and epics, so an estimated epic plus its children counts the work twice.
• Two points fields. On a site with both space types, a Story Points column alone misses every team-managed item. If a points column arrives empty in a Sheets export, see Fix 8 in our Jira Cloud for Sheets troubleshooting guide for the duplicate-name bug.
• Several sprints in one field. Atlassian’s JQL docs note that a work item can belong to completed and open sprints at once. Grouping on the raw Sprint value creates combined labels, and a CSV export can split it into several Sprint columns; group on the current sprint instead. For sprint queries, see our JQL examples for reports.
• Numbers stored as text. If a total is 0 when it shouldn’t be, check a cell with =ISNUMBER(Jira!F2). If it’s FALSE, format the column as Number or re-import.

Share it with leadership

Share the sheet as Viewer and show the refreshed time from Jira!J1 at the top. Jira’s built-in gadgets can’t display a Google Sheets chart, so link the sheet from wherever leadership looks, or use the Automation rule above if one number must sit on a Jira dashboard.

Where Rowduet fits

Rowduet (in development) is a Google Sheets add-on being built to import Jira fields such as story points and other number fields on an hourly or daily schedule, and to email you when a refresh fails, so a stale total doesn’t reach a leadership review. It isn’t available yet. If you send these numbers every week, you can join early access.