Field to sum a column

[kevho1]

Dear All,

I have a list, say column A containing list of salary and I want to calculate the total pension.

The pension is calculated at $1,500 or 0.05*salary, and I am using the formula: Min(1500,0.05*A1) and so on for the rest of the cells.

Is there a way to sum the total of pension without creating a new column?

Thanks.

[bebo021999]

Try this:

=SUM(IF(A1:A4>(1500/0.05),1500/0.05,A1:A4)*0.05)

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[kevho1]

Many thanks. What if I have different sections and therebwere subtotal for the salary of the section?

e.g. A1:A4 include salary, A5 is subtotal of A1:A4, then it repeats for different sections.

any good way to exclude the subtotal rows?

Thanks.

[davsth]

We have now reached the point that a sample workbook would facilitate you getting a better answers. Follow the instructions in the yellow banner. There may be a column that is always populated to exclude or include in the calculation.