[WH99]

Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are several entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31" date 1 to 31
(depending on the month, cell G1). Column "B" the unit and column "C" total
time.
What I am trying to achieve is, in sheet2, to select a month(G1) that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[Bob Phillips[_3_]]

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),
--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31" date 1 to
31
(depending on the month, cell G1). Column "B" the unit and column "C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1) that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[WH99]

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),
--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31" date 1 to
31
(depending on the month, cell G1). Column "B" the unit and column "C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1) that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[Bob Phillips[_3_]]

I assume G1 was just the month text, use this instead

=SUMPRODUCT(--(MONTH(Sheet1!$A$10:$A$50)=MONTH($G$1)),
--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&TEXT($G$1,"mmmm")))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),
--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31" date 1
to
31
(depending on the month, cell G1). Column "B" the unit and column "C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1) that
will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[joel]

first, you need to change bob's formula from 50 rows to 500 rows. This is
not giving you the error.

I think the error is being caused by DATEVALUE(A1&"-"&$G$1)

try putting =DATEVALUE(A1&"-"&$G$1) in a cell to see if this is the cause of
the problem. bob is create a time value that looks like "1 - Jan". You can
enter 1 - Jan in a cell a see if tis produces a valid microsoft data..
syntax for time vaires slightly with different versions of excel in
diffferent countries. Some countries versions may not except the time format
1 - Jan.

Bob's code also expect just a number (1 - 31) in column A to product the
Datevalue.

You can debug the error by using the Evaluate formual in the worksheet menu.
click on the cell with the formula and go to Tools - Formula Auditing -
Evaluate Formula.

"WH99" wrote:

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),
--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31" date 1 to
31
(depending on the month, cell G1). Column "B" the unit and column "C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1) that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[WH99]

Sorry guys,

Bob,
$G$1 is month and year. ie April-08. But with your new code I still get the
#VALUE

Joel,
Still makes no difference still get #VALUE
--
WH99


"Joel" wrote:

first, you need to change bob's formula from 50 rows to 500 rows. This is
not giving you the error.

I think the error is being caused by DATEVALUE(A1&"-"&$G$1)

try putting =DATEVALUE(A1&"-"&$G$1) in a cell to see if this is the cause of
the problem. bob is create a time value that looks like "1 - Jan". You can
enter 1 - Jan in a cell a see if tis produces a valid microsoft data..
syntax for time vaires slightly with different versions of excel in
diffferent countries. Some countries versions may not except the time format
1 - Jan.

Bob's code also expect just a number (1 - 31) in column A to product the
Datevalue.

You can debug the error by using the Evaluate formual in the worksheet menu.
click on the cell with the formula and go to Tools - Formula Auditing -
Evaluate Formula.

"WH99" wrote:

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),
--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31" date 1 to
31
(depending on the month, cell G1). Column "B" the unit and column "C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1) that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[Bob Phillips[_3_]]

Is it a text month and yera or a true date formatted?

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Sorry guys,

Bob,
$G$1 is month and year. ie April-08. But with your new code I still get
the
#VALUE

Joel,
Still makes no difference still get #VALUE
--
WH99


"Joel" wrote:

first, you need to change bob's formula from 50 rows to 500 rows. This
is
not giving you the error.

I think the error is being caused by DATEVALUE(A1&"-"&$G$1)

try putting =DATEVALUE(A1&"-"&$G$1) in a cell to see if this is the cause
of
the problem. bob is create a time value that looks like "1 - Jan". You
can
enter 1 - Jan in a cell a see if tis produces a valid microsoft data..
syntax for time vaires slightly with different versions of excel in
diffferent countries. Some countries versions may not except the time
format
1 - Jan.

Bob's code also expect just a number (1 - 31) in column A to product the
Datevalue.

You can debug the error by using the Evaluate formual in the worksheet
menu.
click on the cell with the formula and go to Tools - Formula Auditing -
Evaluate Formula.

"WH99" wrote:

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),

--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are
several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31"
date 1 to
31
(depending on the month, cell G1). Column "B" the unit and column
"C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1)
that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[WH99]

Bob,
true date format
--
WH99


"Bob Phillips" wrote:

Is it a text month and yera or a true date formatted?

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Sorry guys,

Bob,
$G$1 is month and year. ie April-08. But with your new code I still get
the
#VALUE

Joel,
Still makes no difference still get #VALUE
--
WH99


"Joel" wrote:

first, you need to change bob's formula from 50 rows to 500 rows. This
is
not giving you the error.

I think the error is being caused by DATEVALUE(A1&"-"&$G$1)

try putting =DATEVALUE(A1&"-"&$G$1) in a cell to see if this is the cause
of
the problem. bob is create a time value that looks like "1 - Jan". You
can
enter 1 - Jan in a cell a see if tis produces a valid microsoft data..
syntax for time vaires slightly with different versions of excel in
diffferent countries. Some countries versions may not except the time
format
1 - Jan.

Bob's code also expect just a number (1 - 31) in column A to product the
Datevalue.

You can debug the error by using the Evaluate formual in the worksheet
menu.
click on the cell with the formula and go to Tools - Formula Auditing -
Evaluate Formula.

"WH99" wrote:

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),

--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are
several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31"
date 1 to
31
(depending on the month, cell G1). Column "B" the unit and column
"C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1)
that will
bring up the days of that month down column "A". Then total (by the
individual days) down column "C".
--
WH99

[Bob Phillips[_3_]]

Then my suggestion should have worked.

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Bob,
true date format
--
WH99


"Bob Phillips" wrote:

Is it a text month and yera or a true date formatted?

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Sorry guys,

Bob,
$G$1 is month and year. ie April-08. But with your new code I still get
the
#VALUE

Joel,
Still makes no difference still get #VALUE
--
WH99


"Joel" wrote:

first, you need to change bob's formula from 50 rows to 500 rows.
This
is
not giving you the error.

I think the error is being caused by DATEVALUE(A1&"-"&$G$1)

try putting =DATEVALUE(A1&"-"&$G$1) in a cell to see if this is the
cause
of
the problem. bob is create a time value that looks like "1 - Jan".
You
can
enter 1 - Jan in a cell a see if tis produces a valid microsoft data..
syntax for time vaires slightly with different versions of excel in
diffferent countries. Some countries versions may not except the time
format
1 - Jan.

Bob's code also expect just a number (1 - 31) in column A to product
the
Datevalue.

You can debug the error by using the Evaluate formual in the worksheet
menu.
click on the cell with the formula and go to Tools - Formula
Auditing -
Evaluate Formula.

"WH99" wrote:

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),

--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are
several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31"
date 1 to
31
(depending on the month, cell G1). Column "B" the unit and
column
"C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1)
that will
bring up the days of that month down column "A". Then total (by
the
individual days) down column "C".
--
WH99

[WH99]

Bob,
Many thanks, it works with the following formula. Iv changed some of the
references to match the sheet names and I have increased the rows.

=SUMPRODUCT(--(MONTH(MAIN!$A$10:$A$4999)=MONTH($G$1)),--(DAY(MAIN!$A$10:$A$4999)=DAY($A1)),MAIN!$D$10:$D$4 999)

Thanks for your help. I have another question but I will post a new one.
--
WH99


"Bob Phillips" wrote:

Then my suggestion should have worked.

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Bob,
true date format
--
WH99


"Bob Phillips" wrote:

Is it a text month and yera or a true date formatted?

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Sorry guys,

Bob,
$G$1 is month and year. ie April-08. But with your new code I still get
the
#VALUE

Joel,
Still makes no difference still get #VALUE
--
WH99


"Joel" wrote:

first, you need to change bob's formula from 50 rows to 500 rows.
This
is
not giving you the error.

I think the error is being caused by DATEVALUE(A1&"-"&$G$1)

try putting =DATEVALUE(A1&"-"&$G$1) in a cell to see if this is the
cause
of
the problem. bob is create a time value that looks like "1 - Jan".
You
can
enter 1 - Jan in a cell a see if tis produces a valid microsoft data..
syntax for time vaires slightly with different versions of excel in
diffferent countries. Some countries versions may not except the time
format
1 - Jan.

Bob's code also expect just a number (1 - 31) in column A to product
the
Datevalue.

You can debug the error by using the Evaluate formual in the worksheet
menu.
click on the cell with the formula and go to Tools - Formula
Auditing -
Evaluate Formula.

"WH99" wrote:

Sorry Bob,
I get "#VALUE" in the total colimn.
Cell "G1" I have formatted the date as "April-08".

--
WH99


"Bob Phillips" wrote:

SUMPRODUCT(--(TEXT(Sheet1!$A$10:$A$50,"mmmm")=$G$1),

--(DAY(Sheet1!$A$10:$A$50)=DAY(DATEVALUE(A1&"-"&$G$1))),
Sheet1!$C$10:$C$50)

--
__________________________________
HTH

Bob

"WH99" wrote in message
...
Hi,
I have a data sheet1 as follows:
A B C
Date Unit Time

Data is entered daily, and I might have days which there are
several
entries
or none at all. It covers the whole year.(A10:A5000)
I have inserted a new sheet2, in that I have in column "A1:A31"
date 1 to
31
(depending on the month, cell G1). Column "B" the unit and
column
"C"
total
time.
What I am trying to achieve is, in sheet2, to select a month(G1)
that will
bring up the days of that month down column "A". Then total (by
the
individual days) down column "C".
--
WH99