Summing values from array

[Guest]

I need to sum the values from an array based on an input in a cell. The
table looks like below

A B

Month PT Basic
Hours Paid

07-2004
08-2004 50
09-2004 100
10-2004 50
11-2004 50
12-2004 50
01-2005
02-2005 75
03-2005
04-2005
05-2005

I want to enter 10-2004 in an input cell and then a formula to return the
sum of hours in previous months ie 150. Another formula should then return
mth 10-2004 and any following ie 225.

I have tried =SUM(IF(month<G2,basic_hours)) as an array fomula, where G2 is
the cell I entered 10-2004 in but keep getting zero. A is formatted as text
as is G2

A previous reply did not work.

Thanks for any help

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A20<DATE(YEAR(D1),MONTH(D1),1)),B1:B20)

and

=SUMPRODUCT(--(A1:A20>=DATE(YEAR(D1),MONTH(D10),1)),B1:B20)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob

Thanks but I have entered this formula and still get 0. I assume with this
formula that D1 is the cell I enter 10-2004 in. Are the -- after the first
bracket required. I have tried it with and without but still with the same
answer.

G

 

[Bob Phillips]

They are needed. Are your fields real dates or text?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob

They are currently formatted as Text. Sorry but I did put that in original
posting.

Thanks for the help

 

[Bob Phillips]

Sorry Garth, missed that bit.

To be honest, I would change them all to real dates. Just set them to day 1
and format as mmm-yyyy. Life will be much easier overall if you do.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

About the only practical way you can keep the first column as text & make
this work is if you put the year first, i.e., 2004-07. Then you can use the
formula I gave you yesterday. However, I agree with Bob that using actual
date values is better & easier than working with text values

 

[Guest]

Thank you to the both of you I'll try it with dates.

Didn't mean to 'dis' you Duke by re-posting but when I couldn't get it to
work and was pretty desparate to finish it today I thought that you might be
tucked up in bed so osted again. I thought it must be something to do with
formats as I managed to get a test array with other data in it to work.

Once again thanks