Sum or count cells in entire column except the header one - Excel

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 ?

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.