Exclude Strikethrough Cells In Google Sheets Sum Calculations

Exclude Strikethrough Cells In Google Sheets Sum Calculations

When working with Google Sheets, you may encounter situations where you need to exclude certain cells from calculations, such as summing a range of cells. One common scenario is when you want to ignore cells that have strikethrough formatting applied to them. In this article, we will explore the methods to exclude strikethrough cells in Google Sheets sum calculations.

Why Exclude Strikethrough Cells?

Strikethrough cells often indicate that the data is no longer valid or has been canceled. Including these cells in sum calculations can lead to inaccurate results. By excluding strikethrough cells, you can ensure that your calculations are based only on the relevant and valid data.

Method 1: Using Google Sheets Functions

You can use a combination of Google Sheets functions to exclude strikethrough cells from sum calculations. One approach is to use the FILTER function to remove the strikethrough cells and then apply the SUM function to the filtered range.

Here's an example formula:

=SUM(FILTER(A1:A10, NOT(REGEXMATCH(A1:A10, "~~"))))

In this formula:

• A1:A10 is the range of cells you want to sum.
• NOT(REGEXMATCH(A1:A10, "~~")) is the condition to exclude cells with strikethrough formatting. The REGEXMATCH function checks if the cell contains the strikethrough symbol (~~).
• FILTER removes the cells that match the condition, leaving only the cells without strikethrough formatting.
• SUM calculates the sum of the filtered range.

Method 2: Using Google Sheets Scripts

Google Sheets Scripts

If you prefer a more automated approach, you can use Google Sheets scripts to exclude strikethrough cells from sum calculations. You can create a custom function that checks for strikethrough formatting and returns the sum of the non-strikethrough cells.

Here's an example script:

function sumNonStrikethrough(range) {
  var cells = range.getValues();
  var sum = 0;
  for (var i = 0; i < cells.length; i++) {
    if (!cells[i][0].includes("~~")) {
      sum += cells[i][0];
    }
  }
  return sum;
}

To use this script, follow these steps:

1. Open your Google Sheet.
2. Click on "Tools" > "Script editor".
3. Delete any existing code in the editor and paste the script above.
4. Save the script by clicking on the floppy disk icon or pressing Ctrl+S (or Cmd+S on a Mac).
5. Return to your Google Sheet and select the cell where you want to display the sum.
6. Type =sumNonStrikethrough(A1:A10) and press Enter.

In this script:

• range is the range of cells you want to sum.
• getValues() retrieves the values in the range.
• The for loop checks each cell in the range for strikethrough formatting using the includes() method.
• If a cell does not have strikethrough formatting, its value is added to the sum.
• The return statement returns the sum of the non-strikethrough cells.

Method 3: Using Conditional Formatting

Another approach is to use conditional formatting to highlight the strikethrough cells and then use a formula to sum only the non-highlighted cells.

Here's an example:

1. Select the range of cells you want to sum (A1:A10).
2. Go to the "Format" tab > "Conditional formatting".
3. In the "Format cells if" dropdown, select "Custom formula is".
4. Enter the formula =REGEXMATCH(A1:A10, "~~").
5. Click on the "Format" button and select a highlight color.
6. Click on the "Done" button.

This will highlight the cells with strikethrough formatting.

Next, use the following formula to sum only the non-highlighted cells:

=SUMIF(A1:A10, NOT(ISNUMBER(MATCH(A1:A10, INDIRECT("format range"), 0))))

In this formula:

• A1:A10 is the range of cells you want to sum.
• NOT(ISNUMBER(MATCH(A1:A10, INDIRECT("format range"), 0))) checks if the cell is not highlighted.
• SUMIF sums only the non-highlighted cells.

Gallery of Exclude Strikethrough Cells

Exclude Strikethrough Cells Image Gallery

In conclusion, excluding strikethrough cells from sum calculations in Google Sheets can be achieved using various methods, including using Google Sheets functions, scripts, and conditional formatting. By following these methods, you can ensure that your calculations are accurate and based only on the relevant data.

We hope this article has been helpful in guiding you through the process of excluding strikethrough cells in Google Sheets sum calculations. If you have any questions or need further assistance, please don't hesitate to ask in the comments section below.