I need to sum the values from an array based on an input in a cell. The
table looks like below
A B
MonthPT Basic
Hours Paid
07-2004
08-200450
09-2004100
10-200450
11-200450
12-200450
01-2005
02-200575
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(monthlt;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=SUMPRODUCT(--(A1:A20lt;DATE(YEAR(D1),MONTH(D1),1)),B1:B20)
and
=SUMPRODUCT(--(A1:A20gt;=DATE(YEAR(D1),MONTH(D10),1)),B1:B20)
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Garthquot; gt; wrote in message
...
gt; I need to sum the values from an array based on an input in a cell. The
gt; table looks like below
gt;
gt; A B
gt;
gt; Month PT Basic
gt; Hours Paid
gt;
gt; 07-2004
gt; 08-2004 50
gt; 09-2004 100
gt; 10-2004 50
gt; 11-2004 50
gt; 12-2004 50
gt; 01-2005
gt; 02-2005 75
gt; 03-2005
gt; 04-2005
gt; 05-2005
gt;
gt; I want to enter 10-2004 in an input cell and then a formula to return the
gt; sum of hours in previous months ie 150. Another formula should then
return
gt; mth 10-2004 and any following ie 225.
gt;
gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula, where G2
is
gt; the cell I entered 10-2004 in but keep getting zero. A is formatted as
text
gt; as is G2
gt;
gt; A previous reply did not work.
gt;
gt; Thanks for any help
gt;
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
quot;Bob Phillipsquot; wrote:
gt; =SUMPRODUCT(--(A1:A20lt;DATE(YEAR(D1),MONTH(D1),1)),B1:B20)
gt;
gt; and
gt;
gt; =SUMPRODUCT(--(A1:A20gt;=DATE(YEAR(D1),MONTH(D10),1)),B1:B20)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Garthquot; gt; wrote in message
gt; ...
gt; gt; I need to sum the values from an array based on an input in a cell. The
gt; gt; table looks like below
gt; gt;
gt; gt; A B
gt; gt;
gt; gt; Month PT Basic
gt; gt; Hours Paid
gt; gt;
gt; gt; 07-2004
gt; gt; 08-2004 50
gt; gt; 09-2004 100
gt; gt; 10-2004 50
gt; gt; 11-2004 50
gt; gt; 12-2004 50
gt; gt; 01-2005
gt; gt; 02-2005 75
gt; gt; 03-2005
gt; gt; 04-2005
gt; gt; 05-2005
gt; gt;
gt; gt; I want to enter 10-2004 in an input cell and then a formula to return the
gt; gt; sum of hours in previous months ie 150. Another formula should then
gt; return
gt; gt; mth 10-2004 and any following ie 225.
gt; gt;
gt; gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula, where G2
gt; is
gt; gt; the cell I entered 10-2004 in but keep getting zero. A is formatted as
gt; text
gt; gt; as is G2
gt; gt;
gt; gt; A previous reply did not work.
gt; gt;
gt; gt; Thanks for any help
gt; gt;
gt;
gt;
gt;
They are needed. Are your fields real dates or text?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Garthquot; gt; wrote in message
...
gt; Hi Bob
gt;
gt; Thanks but I have entered this formula and still get 0. I assume with
this
gt; formula that D1 is the cell I enter 10-2004 in. Are the -- after the first
gt; bracket required. I have tried it with and without but still with the same
gt; answer.
gt;
gt; G
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(A1:A20lt;DATE(YEAR(D1),MONTH(D1),1)),B1:B20)
gt; gt;
gt; gt; and
gt; gt;
gt; gt; =SUMPRODUCT(--(A1:A20gt;=DATE(YEAR(D1),MONTH(D10),1)),B1:B20)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Garthquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I need to sum the values from an array based on an input in a cell.
The
gt; gt; gt; table looks like below
gt; gt; gt;
gt; gt; gt; A B
gt; gt; gt;
gt; gt; gt; Month PT Basic
gt; gt; gt; Hours Paid
gt; gt; gt;
gt; gt; gt; 07-2004
gt; gt; gt; 08-2004 50
gt; gt; gt; 09-2004 100
gt; gt; gt; 10-2004 50
gt; gt; gt; 11-2004 50
gt; gt; gt; 12-2004 50
gt; gt; gt; 01-2005
gt; gt; gt; 02-2005 75
gt; gt; gt; 03-2005
gt; gt; gt; 04-2005
gt; gt; gt; 05-2005
gt; gt; gt;
gt; gt; gt; I want to enter 10-2004 in an input cell and then a formula to return
the
gt; gt; gt; sum of hours in previous months ie 150. Another formula should then
gt; gt; return
gt; gt; gt; mth 10-2004 and any following ie 225.
gt; gt; gt;
gt; gt; gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula, where
G2
gt; gt; is
gt; gt; gt; the cell I entered 10-2004 in but keep getting zero. A is formatted
as
gt; gt; text
gt; gt; gt; as is G2
gt; gt; gt;
gt; gt; gt; A previous reply did not work.
gt; gt; gt;
gt; gt; gt; Thanks for any help
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Hi Bob
They are currently formatted as Text. Sorry but I did put that in original
posting.
Thanks for the help
quot;Bob Phillipsquot; wrote:
gt; They are needed. Are your fields real dates or text?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Garthquot; gt; wrote in message
gt; ...
gt; gt; Hi Bob
gt; gt;
gt; gt; Thanks but I have entered this formula and still get 0. I assume with
gt; this
gt; gt; formula that D1 is the cell I enter 10-2004 in. Are the -- after the first
gt; gt; bracket required. I have tried it with and without but still with the same
gt; gt; answer.
gt; gt;
gt; gt; G
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--(A1:A20lt;DATE(YEAR(D1),MONTH(D1),1)),B1:B20)
gt; gt; gt;
gt; gt; gt; and
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(A1:A20gt;=DATE(YEAR(D1),MONTH(D10),1)),B1:B20)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Garthquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I need to sum the values from an array based on an input in a cell.
gt; The
gt; gt; gt; gt; table looks like below
gt; gt; gt; gt;
gt; gt; gt; gt; A B
gt; gt; gt; gt;
gt; gt; gt; gt; Month PT Basic
gt; gt; gt; gt; Hours Paid
gt; gt; gt; gt;
gt; gt; gt; gt; 07-2004
gt; gt; gt; gt; 08-2004 50
gt; gt; gt; gt; 09-2004 100
gt; gt; gt; gt; 10-2004 50
gt; gt; gt; gt; 11-2004 50
gt; gt; gt; gt; 12-2004 50
gt; gt; gt; gt; 01-2005
gt; gt; gt; gt; 02-2005 75
gt; gt; gt; gt; 03-2005
gt; gt; gt; gt; 04-2005
gt; gt; gt; gt; 05-2005
gt; gt; gt; gt;
gt; gt; gt; gt; I want to enter 10-2004 in an input cell and then a formula to return
gt; the
gt; gt; gt; gt; sum of hours in previous months ie 150. Another formula should then
gt; gt; gt; return
gt; gt; gt; gt; mth 10-2004 and any following ie 225.
gt; gt; gt; gt;
gt; gt; gt; gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula, where
gt; G2
gt; gt; gt; is
gt; gt; gt; gt; the cell I entered 10-2004 in but keep getting zero. A is formatted
gt; as
gt; gt; gt; text
gt; gt; gt; gt; as is G2
gt; gt; gt; gt;
gt; gt; gt; gt; A previous reply did not work.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for any help
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;
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)
quot;Garthquot; gt; wrote in message
news
gt; Hi Bob
gt;
gt; They are currently formatted as Text. Sorry but I did put that in original
gt; posting.
gt;
gt; Thanks for the help
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; They are needed. Are your fields real dates or text?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Garthquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi Bob
gt; gt; gt;
gt; gt; gt; Thanks but I have entered this formula and still get 0. I assume with
gt; gt; this
gt; gt; gt; formula that D1 is the cell I enter 10-2004 in. Are the -- after the
first
gt; gt; gt; bracket required. I have tried it with and without but still with the
same
gt; gt; gt; answer.
gt; gt; gt;
gt; gt; gt; G
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(A1:A20lt;DATE(YEAR(D1),MONTH(D1),1)),B1:B20)
gt; gt; gt; gt;
gt; gt; gt; gt; and
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(A1:A20gt;=DATE(YEAR(D1),MONTH(D10),1)),B1:B20)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Garthquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; I need to sum the values from an array based on an input in a
cell.
gt; gt; The
gt; gt; gt; gt; gt; table looks like below
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; A B
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Month PT Basic
gt; gt; gt; gt; gt; Hours Paid
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; 07-2004
gt; gt; gt; gt; gt; 08-2004 50
gt; gt; gt; gt; gt; 09-2004 100
gt; gt; gt; gt; gt; 10-2004 50
gt; gt; gt; gt; gt; 11-2004 50
gt; gt; gt; gt; gt; 12-2004 50
gt; gt; gt; gt; gt; 01-2005
gt; gt; gt; gt; gt; 02-2005 75
gt; gt; gt; gt; gt; 03-2005
gt; gt; gt; gt; gt; 04-2005
gt; gt; gt; gt; gt; 05-2005
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to enter 10-2004 in an input cell and then a formula to
return
gt; gt; the
gt; gt; gt; gt; gt; sum of hours in previous months ie 150. Another formula should
then
gt; gt; gt; gt; return
gt; gt; gt; gt; gt; mth 10-2004 and any following ie 225.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula,
where
gt; gt; G2
gt; gt; gt; gt; is
gt; gt; gt; gt; gt; the cell I entered 10-2004 in but keep getting zero. A is
formatted
gt; gt; as
gt; gt; gt; gt; text
gt; gt; gt; gt; gt; as is G2
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; A previous reply did not work.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for any help
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
About the only practical way you can keep the first column as text amp; 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 amp; easier than working with text valuesquot;Garthquot; wrote:
gt; I need to sum the values from an array based on an input in a cell. The
gt; table looks like below
gt;
gt; A B
gt;
gt; MonthPT Basic
gt; Hours Paid
gt;
gt; 07-2004
gt; 08-200450
gt; 09-2004100
gt; 10-200450
gt; 11-200450
gt; 12-200450
gt; 01-2005
gt; 02-200575
gt; 03-2005
gt; 04-2005
gt; 05-2005
gt;
gt; I want to enter 10-2004 in an input cell and then a formula to return the
gt; sum of hours in previous months ie 150. Another formula should then return
gt; mth 10-2004 and any following ie 225.
gt;
gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula, where G2 is
gt; the cell I entered 10-2004 in but keep getting zero. A is formatted as text
gt; as is G2
gt;
gt; A previous reply did not work.
gt;
gt; Thanks for any help
gt;
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
quot;Duke Careyquot; wrote:
gt; About the only practical way you can keep the first column as text amp; make
gt; this work is if you put the year first, i.e., 2004-07. Then you can use the
gt; formula I gave you yesterday. However, I agree with Bob that using actual
gt; date values is better amp; easier than working with text values
gt;
gt;
gt; quot;Garthquot; wrote:
gt;
gt; gt; I need to sum the values from an array based on an input in a cell. The
gt; gt; table looks like below
gt; gt;
gt; gt; A B
gt; gt;
gt; gt; MonthPT Basic
gt; gt; Hours Paid
gt; gt;
gt; gt; 07-2004
gt; gt; 08-200450
gt; gt; 09-2004100
gt; gt; 10-200450
gt; gt; 11-200450
gt; gt; 12-200450
gt; gt; 01-2005
gt; gt; 02-200575
gt; gt; 03-2005
gt; gt; 04-2005
gt; gt; 05-2005
gt; gt;
gt; gt; I want to enter 10-2004 in an input cell and then a formula to return the
gt; gt; sum of hours in previous months ie 150. Another formula should then return
gt; gt; mth 10-2004 and any following ie 225.
gt; gt;
gt; gt; I have tried =SUM(IF(monthlt;G2,basic_hours)) as an array fomula, where G2 is
gt; gt; the cell I entered 10-2004 in but keep getting zero. A is formatted as text
gt; gt; as is G2
gt; gt;
gt; gt; A previous reply did not work.
gt; gt;
gt; gt; Thanks for any help
gt; gt;
留言列表
