Summing values from array
 

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

Summing values from array
 

=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)

"Garth" wrote in message
...
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

Summing values from array
 

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" wrote:

=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)

"Garth" wrote in message
...
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

Summing values from array
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Garth" wrote in message
...
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" wrote:

=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)

"Garth" wrote in message
...
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

Summing values from array
 

Hi Bob

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

Thanks for the help

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Garth" wrote in message
...
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" wrote:

=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)

"Garth" wrote in message
...
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

Summing values from array
 

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)

"Garth" wrote in message
...
Hi Bob

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

Thanks for the help

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Garth" wrote in message
...
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" wrote:

=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)

"Garth" wrote in message
...
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

Summing values from array
 

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


"Garth" wrote:

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

Summing values from array
 

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

"Duke Carey" wrote:

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


"Garth" wrote:

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