Summing cells in Calc using changing criteria

[stewartie]

Hi,

I am new to Calc and would appreciate some help with the following problem. I need to sum selected cells in a spreadsheet based on a criteria which will change.

Restated I want to sum a number of cells when the header (or some identifier) for these cells is less than, equal to or more than a 'key' cell. The key cell is the changing criteria. I did find a solution to the problem in excel on the following webpage http://visualbasic.about.com/od/learnvb ... umvals.htm

All suggestions welcome.

[Villeroy]

Function DSUM can do this in the most convenient manner. See help file on DSUM and this example on DAVERAGE http://www.openofficetips.com/2004/11/1 ... -daverage/

[Dave]

You didn't state which condition is necessary for the summation, or what the others will do. That is, do you sum if the key cell is more, or if it is less? What do you expect to see if the key cell is not the summation key?

David.

[stewartie]

Thanks for the prompt response.

The cells being summed are the result cells of DSUM/DCOUNT/DAVERAGES functions. The condition necessary for the summation is that the header cells are <,> or equal to the key cell. So for example:

If the current value in the key cell = 4 and the static values of the header cells of 10 columns = 0,1,2....9
and there are a number of rows, I want three separate sums for each row - one sum of the cells where the header cells are <4 ('sum 1'), one sum of the cells where the header cells are >4 ('sum 2') and one for the cell whose header = 4 ('sum 3'?).

In this example 'sum 1' would sum columns headed 0,1,2,3 for each row, 'sum 2' would sum columns headed 5, 6, 7, 8 and 9 for each row and 'sum 3' would return the value for the column headed 4 for each row. i realise the last sum isn't really a sum but I need to be able to reference it when the key cell value changes. Hope this helps - I would have posted a screen-print but I'm not too familiar wih this format either.

Many thanks again.

[Dave]

Across A1 to J1, I have 0,1, 2, ... The key value is in M1.

In A2, I have =$M$1-A1. Copy across row 2.

Values for row 3 span A3 through J3

In L3, I have =SUMIF(A2:J2;">0";A3:J3)
In M3, I have =SUMIF(A2:J2;"=0";A3:J3)
In N3, I have =SUMIF(A2:J2;"<0";A3:J3)

Enjoy!

David.

[stewartie]

David,

Many thanks for your contribution. Exactly what I was looking for.

ELEGANT!

Regards,
Stewartie