Hi, how can I sum or count cells in entire excel column, except the first one in the header ? How to select entire column, but exclude a certain rows ? How should the formula look like ?

07 Mar 2023 at 05:44 PM

Hi,

You can use several methods how to sum or count cells in entire column, excluding some cells. Sometimes it is not possible to do it with single formula, so you may need to use the combination of multiple formulas. The following solutions are based on deduction, but in some cases, you can use logical operators.

**=SUM(C:C)**

The formula above will sum all number values in entire Column C. Cell values that contain text will be not included in sum result. So if table header contain text, it will not affect the calculation.

**=SUM(C2:C1048576)**

The fomula above will sum all number values in defined range of rows. If possible, you can enter the row number, that should not be reached. For example, if you know that your table will not have more than 1000 rows, then the range will be C2:C1000. Maximum number of rows is 1048576 for the recent Excel versions.

**=SUM(C:C)-SUM(C1:C1)-SUM(C10:C15)**

The formula above allows to exclude the specific cells from the calculation. Simlpy sum all cells in Column C in the first step and then deduct the values from the specific ranges of cells.

**=SUMIF(C:C, "<>1")**

The formula above is based on not equal operator. It will sum number values in entire column, except the cells where value is 1. Cells that contain text or non-number values will be not included in sum result.

**=SUMIF(C:C, ">5")**

The formula above will sum number values in entire column, where values are greater than 5. Cells that contain text or non-number values will be not included in sum result.

**=COUNT(C:C)**

The formula above will count all number values in entire Column C. Cell values that contain text will be not included in the result. So if table header contain text, it will not affect the calculation.

**=COUNT(C2:C1048576)**

The fomula above will count cells within the defined range of rows. If possible, you can enter the row number, that should not be reached. For example, if you know that your table will not have more than 1000 rows, then the range will be C2:C1000. Maximum number of rows is 1048576 for the recent Excel versions.

**=COUNT(C:C)-COUNT(C1:C1)-COUNT(C10:C15)**

The formula above allows to exclude the specific cells from the calculation. Simlpy count all cells in Column C in the first step and then deduct values from the specific ranges of cells.

**=COUNTIF(C:C, "<>")**

The formula above is based on not equal operator. It will count cells in entire column, except the blank cells with no value.

**=COUNTIF(C:C, "<>1")**

The formula above is based on not equal operator. It will count cells with number values in entire column, except the cells where value is 1. Cells that contain text or non-number values will be not included in the result.

**=COUNTIF(C:C, ">5")**

The formula above will count cells with number values in entire column, where values are greater than 5. Cells that contain text or non-number values will be not included in sum result.

You can use several methods how to sum or count cells in entire column, excluding some cells. Sometimes it is not possible to do it with single formula, so you may need to use the combination of multiple formulas. The following solutions are based on deduction, but in some cases, you can use logical operators.

The formula above will sum all number values in entire Column C. Cell values that contain text will be not included in sum result. So if table header contain text, it will not affect the calculation.

The fomula above will sum all number values in defined range of rows. If possible, you can enter the row number, that should not be reached. For example, if you know that your table will not have more than 1000 rows, then the range will be C2:C1000. Maximum number of rows is 1048576 for the recent Excel versions.

The formula above allows to exclude the specific cells from the calculation. Simlpy sum all cells in Column C in the first step and then deduct the values from the specific ranges of cells.

The formula above is based on not equal operator. It will sum number values in entire column, except the cells where value is 1. Cells that contain text or non-number values will be not included in sum result.

The formula above will sum number values in entire column, where values are greater than 5. Cells that contain text or non-number values will be not included in sum result.

The formula above will count all number values in entire Column C. Cell values that contain text will be not included in the result. So if table header contain text, it will not affect the calculation.

The fomula above will count cells within the defined range of rows. If possible, you can enter the row number, that should not be reached. For example, if you know that your table will not have more than 1000 rows, then the range will be C2:C1000. Maximum number of rows is 1048576 for the recent Excel versions.

The formula above allows to exclude the specific cells from the calculation. Simlpy count all cells in Column C in the first step and then deduct values from the specific ranges of cells.

The formula above is based on not equal operator. It will count cells in entire column, except the blank cells with no value.

The formula above is based on not equal operator. It will count cells with number values in entire column, except the cells where value is 1. Cells that contain text or non-number values will be not included in the result.

The formula above will count cells with number values in entire column, where values are greater than 5. Cells that contain text or non-number values will be not included in sum result.

Related questions

## How to add side by side rows in excel pivot table ?

## Is CSV excel file Comma or Semicolon separated / delimited ?

## Check if value exists in column more than once or twice - Excel

## How to change the font of equation formula in Word ?

## How to show Euro, Dollar or Pound sign on right in Excel ?

## Zero value not showing on Excel chart vertical axis - how to display ?

## Move excel chart horizontal axis to bottom - below negative values

## Create two Charts from one Pivot table - add multiple excel charts

## Invalid cell reference error in Excel vLookup function - #REF!

## Excel and Word menu tabs & tools in toolbars disabled / grayed out

Trending questions

Samsung Galaxy A & F better than M series ? What's the difference ? Is PLS LCD display good ? Difference between PLS LCD and Amoled Man Month calculation - how many man days or man hours ? Remove or hide bottom navigation bar with buttons on Android How to calculate inverse currency exchange rate ? How to add side by side rows in excel pivot table ? ATM withdrawal With or Without conversion - Accept or Decline ? What makes a good phone camera ? Important key camera features How to calculate how many Man Days are 3 Man Hours ? How to change QWERTZ keyboard to QWERTY in Windows 10 ?

Samsung Galaxy A & F better than M series ? What's the difference ? Is PLS LCD display good ? Difference between PLS LCD and Amoled Man Month calculation - how many man days or man hours ? Remove or hide bottom navigation bar with buttons on Android How to calculate inverse currency exchange rate ? How to add side by side rows in excel pivot table ? ATM withdrawal With or Without conversion - Accept or Decline ? What makes a good phone camera ? Important key camera features How to calculate how many Man Days are 3 Man Hours ? How to change QWERTZ keyboard to QWERTY in Windows 10 ?

2023 AnswerTabsTermsContact us