software

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.


=sumproduct(--(A1:A100lt;testdate/testvalue),B1:B100)
=sumproduct(--(A1:A100gt;=testdate/testvalue),B1:B100)quot;Garthquot; wrote:

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

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 ?

Gquot;Duke Careyquot; wrote:

gt;
gt; =sumproduct(--(A1:A100lt;testdate/testvalue),B1:B100)
gt; =sumproduct(--(A1:A100gt;=testdate/testvalue),B1:B100)
gt;
gt;
gt; quot;Garthquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; Using Excel XP I have a table with months in column A designated as 08-2004,
gt; gt; 09-2004, 10-2004 etc. In column B against each month I have a number
gt; gt; representing hours. What I want to do is input in a cell on another sheet a
gt; gt; selected month ie 10-2004 and then return two values into selected cells:
gt; gt; 1) The sum of the hours in any months preceding the date I have entered
gt; gt; 2) The sum of the hours in any months after the date I entered plus the
gt; gt; hours in the month entered.
gt; gt;
gt; gt; As always any assistance will be gratefully received.