Sumproduct? by date

[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".

 

[Bob Phillips]

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)

 

[WH99]

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

 

[Bob Phillips]

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)

 

[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]

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

 

[Bob Phillips]

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

 

[WH99]

Bob,
true date format
--
WH99

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

--
__________________________________
HTH

Bob

 

[Bob Phillips]

Then my suggestion should have worked.

 

[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$4999)

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