Summing cells in array
 

Hi

Using Excel XP I have a table with months in column A designated as 08-2004,
09-2004, 10-2004 etc. In column B against each month I have a number
representing hours. What I want to do is input in a cell on another sheet a
selected month ie 10-2004 and then return two values into selected cells:
1) The sum of the hours in any months preceding the date I have entered
2) The sum of the hours in any months after the date I entered plus the
hours in the month entered.

As always any assistance will be gratefully received.

Summing cells in array
 

=sumproduct(--(A1:A100<testdate/testvalue),B1:B100)
=sumproduct(--(A1:A100=testdate/testvalue),B1:B100)


"Garth" wrote:

Hi

Using Excel XP I have a table with months in column A designated as 08-2004,
09-2004, 10-2004 etc. In column B against each month I have a number
representing hours. What I want to do is input in a cell on another sheet a
selected month ie 10-2004 and then return two values into selected cells:
1) The sum of the hours in any months preceding the date I have entered
2) The sum of the hours in any months after the date I entered plus the
hours in the month entered.

As always any assistance will be gratefully received.

Summing cells in array
 

Hi

I have tried inputting this formula but it returns 0

A sample of data might be:

A B
Mth Hours
07-2005 50
08-2005 100
09-2005 50
10-2005 75
11-2005 50
12-2005 50

If I enter a test value of 09-2005 I need the formula to return 150 for the
first value and 225 for the second.

I have tried entering this as a standard formula and an array formula.

What am I doing wrong ?

G


"Duke Carey" wrote:


=sumproduct(--(A1:A100<testdate/testvalue),B1:B100)
=sumproduct(--(A1:A100=testdate/testvalue),B1:B100)


"Garth" wrote:

Hi

Using Excel XP I have a table with months in column A designated as 08-2004,
09-2004, 10-2004 etc. In column B against each month I have a number
representing hours. What I want to do is input in a cell on another sheet a
selected month ie 10-2004 and then return two values into selected cells:
1) The sum of the hours in any months preceding the date I have entered
2) The sum of the hours in any months after the date I entered plus the
hours in the month entered.

As always any assistance will be gratefully received.