|
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
Sumproduct? by date
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 |
| All times are GMT +1. The time now is 11:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com