Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
Hi,
I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT formula may be better) based on a date. I have a spreadsheet that is linked to a database and it displays individual entries for each work task that is entered into it throughout the day. I'd like to be able to add up how much time is spent on a particular task (such as processing e-mails) at the end of the day. I've seen a formula in another post that I think is very close to what I need, but I can't get it to work. I'm pretty sure it's because my date entries also include a time and no two of these are alike. The formula which I think I need to base my formula on is: =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150) Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and column R contains the time spent on the task (in minutes). I just need a slight modification so that instead of using "June" I want to base it on a date that I enter into another field, say C1. Just to complicate matters further, the data is in a pivot table. I'm not sure if that makes a difference. Thanks in advance for any help you can offer. Scott |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
With the date of interest input in C1,
Try in say, T5: =IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Scopar" wrote: Hi, I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT formula may be better) based on a date. I have a spreadsheet that is linked to a database and it displays individual entries for each work task that is entered into it throughout the day. I'd like to be able to add up how much time is spent on a particular task (such as processing e-mails) at the end of the day. I've seen a formula in another post that I think is very close to what I need, but I can't get it to work. I'm pretty sure it's because my date entries also include a time and no two of these are alike. The formula which I think I need to base my formula on is: =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150) Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and column R contains the time spent on the task (in minutes). I just need a slight modification so that instead of using "June" I want to base it on a date that I enter into another field, say C1. Just to complicate matters further, the data is in a pivot table. I'm not sure if that makes a difference. Thanks in advance for any help you can offer. Scott |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
Time is irrelevant, that should work but the ranges MUST be the same size
either =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R8:R150) or =SUMPRODUCT(--(TEXT(F5:F150,"mmmm")="June"),R5:R150) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Scopar" wrote in message ... Hi, I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT formula may be better) based on a date. I have a spreadsheet that is linked to a database and it displays individual entries for each work task that is entered into it throughout the day. I'd like to be able to add up how much time is spent on a particular task (such as processing e-mails) at the end of the day. I've seen a formula in another post that I think is very close to what I need, but I can't get it to work. I'm pretty sure it's because my date entries also include a time and no two of these are alike. The formula which I think I need to base my formula on is: =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150) Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and column R contains the time spent on the task (in minutes). I just need a slight modification so that instead of using "June" I want to base it on a date that I enter into another field, say C1. Just to complicate matters further, the data is in a pivot table. I'm not sure if that makes a difference. Thanks in advance for any help you can offer. Scott |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
Perfect - thanks Max.
Just out of curiosity, what does the "--" in "SUMPRODUCT(--(" actually do? Thanks again, Scott "Max" wrote: With the date of interest input in C1, Try in say, T5: =IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Scopar" wrote: Hi, I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT formula may be better) based on a date. I have a spreadsheet that is linked to a database and it displays individual entries for each work task that is entered into it throughout the day. I'd like to be able to add up how much time is spent on a particular task (such as processing e-mails) at the end of the day. I've seen a formula in another post that I think is very close to what I need, but I can't get it to work. I'm pretty sure it's because my date entries also include a time and no two of these are alike. The formula which I think I need to base my formula on is: =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150) Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and column R contains the time spent on the task (in minutes). I just need a slight modification so that instead of using "June" I want to base it on a date that I enter into another field, say C1. Just to complicate matters further, the data is in a pivot table. I'm not sure if that makes a difference. Thanks in advance for any help you can offer. Scott |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
Thanks Bob.
Sorry, that was a typo. I didn't realise the ranges had to be the same size, so it's good to know that, but they were actually the same size in the formula - just not when I typed it this time around. Thanks, Scott "Bob Phillips" wrote: Time is irrelevant, that should work but the ranges MUST be the same size either =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R8:R150) or =SUMPRODUCT(--(TEXT(F5:F150,"mmmm")="June"),R5:R150) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Scopar" wrote in message ... Hi, I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT formula may be better) based on a date. I have a spreadsheet that is linked to a database and it displays individual entries for each work task that is entered into it throughout the day. I'd like to be able to add up how much time is spent on a particular task (such as processing e-mails) at the end of the day. I've seen a formula in another post that I think is very close to what I need, but I can't get it to work. I'm pretty sure it's because my date entries also include a time and no two of these are alike. The formula which I think I need to base my formula on is: =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150) Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and column R contains the time spent on the task (in minutes). I just need a slight modification so that instead of using "June" I want to base it on a date that I enter into another field, say C1. Just to complicate matters further, the data is in a pivot table. I'm not sure if that makes a difference. Thanks in advance for any help you can offer. Scott |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Scopar" wrote in message ... Perfect - thanks Max. Just out of curiosity, what does the "--" in "SUMPRODUCT(--(" actually do? Thanks again, Scott "Max" wrote: With the date of interest input in C1, Try in say, T5: =IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Scopar" wrote: Hi, I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT formula may be better) based on a date. I have a spreadsheet that is linked to a database and it displays individual entries for each work task that is entered into it throughout the day. I'd like to be able to add up how much time is spent on a particular task (such as processing e-mails) at the end of the day. I've seen a formula in another post that I think is very close to what I need, but I can't get it to work. I'm pretty sure it's because my date entries also include a time and no two of these are alike. The formula which I think I need to base my formula on is: =SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150) Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and column R contains the time spent on the task (in minutes). I just need a slight modification so that instead of using "June" I want to base it on a date that I enter into another field, say C1. Just to complicate matters further, the data is in a pivot table. I'm not sure if that makes a difference. Thanks in advance for any help you can offer. Scott |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
Scopar, thanks for calling back. Glad it fit what you were after.
The "--" in the part: --(INT(F5:F150)=C1) gently coerces the TRUE/FALSE returns in the comparison done within the outer parens, ie: INT(F5:F150)=C1, to 1's/0's. This conversion is required to enable SUMPRODUCT to then "cross-multiply" the 1's/0's with the corresponding numbers within R5:R150 and total the resulting products. Do take the time/effort to study Bob's excellent paper on SUMPRODUCT at his link. You'd definitely benefit a lot from it, as I and countless others did. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Scopar" wrote in message ... Perfect - thanks Max. Just out of curiosity, what does the "--" in "SUMPRODUCT(--(" actually do? Thanks again, Scott |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
"Max" wrote in message ... ... gently coerces the TRUE/FALSE ... I didn't realise you could get poetical about Excel <vbg. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct using dates
"Bob Phillips" wrote:
... gently coerces the TRUE/FALSE ... I didn't realise you could get poetical about Excel <vbg. Ahh, must have been the passion in me then <bg -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
Need to pull current dates from list w/many dates | Excel Discussion (Misc queries) | |||
Default Dates | Excel Discussion (Misc queries) | |||
sumproduct between two dates | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |