Skip down to the research record

SUMIF totals by deal stage: 5 forecast checks

Quota Forecast Editorial Team · Published · 12 min read

SUMIF totals deal values for a stage by matching its label and adding corresponding values (Microsoft Support).

Key takeaways

  • Match the criteria range and the sum range to the same rows before you trust a stage total.
  • Write each criterion in a supported form, such as a stage cell reference.
  • Check matched text for strings longer than 255 characters or #VALUE!.
  • Switch to SUMIFS when a stage total depends on a second condition.
  • Record who can edit or see stage records, and keep a note of the settings that control access.

You can test one column and add values from another, according to Microsoft Support. For a stage total, the stage column is the test and the deal value column is what you add up, so both ranges must line up row for row.

Why stage totals need checking

A stage total is often the number a revenue leader carries into the forecast call. If that total comes from a formula, the formula deserves the same review as the deal list it reads from. Your sheet may have a stage column, a value column and a total row, and each part is worth a look before the total is used.

A stage total built with SUMIF depends on which cells the function reads. Read the formula text in the cell before you read the total beneath it, since the formula decides which rows are added and the result only shows the sum. Start the review with the ranges and criteria, then move to the number. Keep that order each time the sheet is updated, because new rows and renamed stages change what the formula reads.

Start with a short list of the stage names your sheet uses. Each label on that list should match the stage column consistently, including spelling and spacing, so that one stage has one name. Keep the list in a separate area of the workbook, where you can read it beside the formula. A list kept this way makes an error in a label easier to spot.

Stage names can change during a quarter, for example when your team renames a stage or adds one. When that happens, update the list first, then check that the formula points at the current name. Retire the old name from the list so that nobody copies it into a new row.

Filter the sheet to one stage, add its visible deal values by hand, and write that sum beside the formula result. Your comparison does not need to be exact to the cent to be useful, since the goal is to catch a large gap. A large gap is a reason to stop and review the formula before the total goes into the forecast. Keep the hand sum with the formula notes so the next review can repeat the same comparison.

Use this table to find each check in the sections below.

CheckWhere to read it
Matching rangesThe first and last row of the stage range and the value range
Criterion formThe stage label typed once in its own cell
Text lengthThe longest matched label
More conditionsA second condition, such as the deal owner, in its own column
Edit accessThe stage access note and the current settings

Before: set the criteria range and sum range

Match the criteria range to the sum range

Start with the ranges in your stage formula. One range holds the stage labels, and the other holds the deal values that get added up. Each range should cover the same rows, from the first deal row to the last deal row in the list. If one range starts or ends on a different row, the two ranges no longer describe the same deals, so check both the first and last row of each range before you save the formula.

The formula =SUMIF(B2:B5,"John",C2:C5) adds values in C2:C5 where the corresponding cells in B2:B5 match John, according to Microsoft Support. In your sheet, the stage labels take the place of the cells that hold John, and the deal values take the place of the C cells.

Your first deal row should sit on the same line in both columns. Your last deal row should match as well, so that no deal sits outside the range. When you add a deal below the current last row, extend both ranges so the new deal is counted in the stage total. If a deal row has a stage label but no value, decide how that row should be handled before you total the column, and write that decision in a note beside the sheet.

For more mistakes that alter these totals, read conditional pipeline sums.

A visible check beside the stage total makes the comparison easy to repeat. When a stage fails the check, go back to its rows first, before you change the formula.

Illustrative example: total a stage

Suppose B2 and B5 contain the stage label "qualified", while the other rows contain another stage. The corresponding deal values in C2 and C5 are 10 and 0; another stage has a deal value of 10. The formula =SUMIF(B2:B5,B5,C2:C5) returns 10. B2:B5 is the stage range, B5 holds the criterion, and C2:C5 is the value range. The hand-added sum is also 10: add the matching values of 10 and 0, excluding the other stage's value. These values are illustrative.

Before: write the criteria correctly

Write the criterion in a supported form

A criterion is the test that decides which rows count. In a stage total, the stage name is the criterion, and it should be written as it appears in the stage column. Type the stage name in one cell, then point the formula at that cell instead of typing the name inside the formula each time. That gives you one place to correct a spelling mistake, and every formula that uses it updates from the same cell.

A criterion can be a number, a comparison, a cell reference or a function such as TODAY(). The source's example reads, "For example, criteria can be expressed as 32, \">32\", B5, \"3?\", \"apple\", \"~?\", or TODAY().", according to Microsoft Support. A stage label is a word, so the criterion that matches it is the text form. A cell reference, such as B5, is the form to prefer when the stage name lives in a cell you already maintain. Choose the form that fits the column you are testing.

Your value column may also need a threshold, such as deals above a set amount, and that threshold can sit in a cell that the formula compares against. Keep each criterion's form matched to the column it tests, so the formula reads the same kind of value.

The source's comparison example uses numbers. "For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5.", according to Microsoft Support. A comparison such as larger than a threshold applies to a numeric column, so check that your value cells hold numbers rather than text before you compare them.

Your first test can use one stage whose deal values you can add up by hand. When the formula returns that total, copy it to the other stages. Check each copied formula so its criteria cell points at the stage it belongs to. Keep a short list of which cell each stage uses, so a later reviewer can follow the pattern. A stage that was typed by hand rather than linked to a cell is the first place to look when a total looks wrong.

During: watch the text length

Inspect long matched strings

This check applies when the text you match is long. Your stage labels may be short, but other text columns on the same sheet may hold longer entries, such as notes or descriptions. A criterion that points at a long entry is the case to check, since the limit concerns the strings you match. Read the length of the longest matched text before you finalize a stage total, and keep the result in your notes.

SUMIF returns incorrect results on strings longer than 255 characters, according to Microsoft Support. That is a limit on the matched text, so a long label or note in the criteria column is the thing to inspect. If your stage column is short, the check should be quick.

Your sheet may contain a long entry in a cell you match, so a length check on those cells is worth running. Look first at the criteria cells, then at the matched range, since both can hold text. If a criteria cell carries a long note, the match can be wrong even when the stage label looks correct.

A second case in that source is the string #VALUE!, according to Microsoft Support. If a cell in the matched range shows that text, correct it before you read the total. Your scan of the column can run in the same pass as the length check.

When a stage total looks off, inspect the matched labels and any error text in the matched range. Your note of any failing label can guide a shorter stage name in the sheet. Keep the shortened names in the same list you built for the stage names, so the change is recorded in one place. Then recalculate the total and compare it with the hand sum from the earlier check.

During: check additional conditions

Separate the stage and owner tests

Some stage totals need a second condition, such as the deal owner. For that case, the source points to another function, and its wording is, "To sum cells based on multiple criteria, see SUMIFS function.", according to Microsoft Support.

Your next step with a second condition is to name each condition in its own column, so you can read what each part tests. The owner condition should sit beside the stage condition rather than inside one long test. Separate columns make it easier to see which part of the formula is wrong when the total disagrees with your hand sum.

Keep a note of what each part of the formula tests, in plain words such as "stage equals the named stage" and "owner equals the named owner." That note lets a later reviewer check the logic without rebuilding it. Keep it with the formula and the list of stage names, so the three stay together.

Test the combined total against a sum you add up by hand. For example, filter the sheet to one owner and one stage, read the visible values, and compare their sum with the formula's result. Repeat the test for each owner in turn, so that every owner's rows get the same check.

If the combined total disagrees with the hand sum, check the criteria first. Then check the ranges, because a range that covers different rows changes which values the total adds. Record what you changed in the change note, along with the reason, so the correction can be traced later.

After: keep a record of the formula and rules

Record who can edit or see the stage data

Access to the records behind a stage total is part of the same review. According to HubSpot, pipeline rules can limit who can edit records in certain stages. A stage total built on records that anyone can change is worth noting, since the total depends on those records staying as they were.

Your note should name each stage that limits editing, along with the people or groups allowed to edit it. Keep it beside the formula notes, so the reasons for the total and its access rules stay together. Include the date of the last check, so the note shows when the settings were last confirmed.

Your review before each forecast call can compare that note with the current settings. A mismatch between the note and the settings is a reason to check the total again before it goes into the forecast.

Your change log can record the date, the cell range that changed, and the reason for each change. A reader who inherits the sheet can use that log to trace how the total was built. Keep the log in the same place as the formula notes and the access note, so one folder answers the questions a reviewer is likely to ask.

Use a record like this illustrative example to explain a correction.

RecordRange checkedChangeReason
ExampleStage and value columnsExtended both rangesIncluded the added deal row

If your stage records live in Pipedrive, its visibility settings are under Settings > Manage Users > Visibility groups, according to Pipedrive.

For related reviews, read stage duration history checks and forecast total mismatches.

Your next step is to open your pipeline sheet today and confirm that every stage total uses matched criteria and sum ranges.

Common questions

What does SUMIF do?

In a stage total, SUMIF adds deal values for the stage you name. Read the formula text to identify the stage cell and the value range before reviewing its result.

When should I use SUMIFS?

Use SUMIFS when a stage total depends on a second condition, such as the deal owner. Your stage criterion and owner criterion can sit in separate cells, so each one can be checked.

What happens with strings over 255 characters?

SUMIF can return an incorrect total for those strings. Measure the longest matched label, then shorten a failing label and recalculate before using the result.

Can I sum one range and test another?

Yes, the stage column can serve as the test while the value column supplies the amounts. Review the first and last deal rows in both columns to check their alignment.

What forms can a criterion take?

A criterion can use text, a number, a comparison, a cell reference or a function. For a stage name you maintain in a cell, use that cell reference so a spelling correction has one place to go.

Who can edit stage records?

The answer depends on the current access settings for those records. Your team can check those rules before a forecast call. Compare them with the stage access note and investigate any mismatch before using the total.

Sources