Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF() confusion (Excel 2003)
I have a table with named ranges:
A = Date F = LWOP I am trying to calculate the LWOP taken in the CURRENT calendar year. My formula is yielding a result of 0, even though there are 8 hours of LWOP with a date of 2010. These are the formulas I have tried: =SUMIF(Date,"YEAR(Date)=YEAR(TODAY())",LWOP) =SUMIF(Date,IF("Year(Date)=2010",TRUE),LWOP) (I also tried both of those without the quotation marks.) Looking at the posts here, I saw one that seemed similar from last September and I tried this: =SUMIF(Date,YEAR(Date) "=" & YEAR(TODAY()),LWOP) Excel tells me I have an error with that one and won't even let me save the formula. I'm really perplexed. Can anyone advise me how to fix this? (I know I'm going to smack myself in the head when someone tells me what I'm doing wrong.) As always, thanks so much for your help. -- Ann Scharpf |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF() confusion (Excel 2003)
Try
=SUMPRODUCT(--(YEAR(Date)=YEAR(TODAY())),LWOP) HTH Bob "Ann Scharpf" wrote in message ... I have a table with named ranges: A = Date F = LWOP I am trying to calculate the LWOP taken in the CURRENT calendar year. My formula is yielding a result of 0, even though there are 8 hours of LWOP with a date of 2010. These are the formulas I have tried: =SUMIF(Date,"YEAR(Date)=YEAR(TODAY())",LWOP) =SUMIF(Date,IF("Year(Date)=2010",TRUE),LWOP) (I also tried both of those without the quotation marks.) Looking at the posts here, I saw one that seemed similar from last September and I tried this: =SUMIF(Date,YEAR(Date) "=" & YEAR(TODAY()),LWOP) Excel tells me I have an error with that one and won't even let me save the formula. I'm really perplexed. Can anyone advise me how to fix this? (I know I'm going to smack myself in the head when someone tells me what I'm doing wrong.) As always, thanks so much for your help. -- Ann Scharpf |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF() confusion (Excel 2003)
Ann Scharpf wrote:
I have a table with named ranges: A = Date F = LWOP I am trying to calculate the LWOP taken in the CURRENT calendar year. My formula is yielding a result of 0, even though there are 8 hours of LWOP with a date of 2010. These are the formulas I have tried: =SUMIF(Date,"YEAR(Date)=YEAR(TODAY())",LWOP) =SUMIF(Date,IF("Year(Date)=2010",TRUE),LWOP) (I also tried both of those without the quotation marks.) Looking at the posts here, I saw one that seemed similar from last September and I tried this: =SUMIF(Date,YEAR(Date) "=" & YEAR(TODAY()),LWOP) Excel tells me I have an error with that one and won't even let me save the formula. I'm really perplexed. Can anyone advise me how to fix this? (I know I'm going to smack myself in the head when someone tells me what I'm doing wrong.) As always, thanks so much for your help. Try this: =SUMPRODUCT(--(YEAR(Date)=YEAR(TODAY())),LWOP) Also, I suggest NOT using a defined name that is the same as a function name. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF() confusion (Excel 2003)
Thanks to both of you for posting about the SUMPRODUCT(). I thought I only
needed to use that function if I was looking at two or more conditions. I've also never used the DATE() function. Didn't realize it was a function. I'll rename my range. -- Ann Scharpf "Glenn" wrote: Ann Scharpf wrote: I have a table with named ranges: A = Date F = LWOP I am trying to calculate the LWOP taken in the CURRENT calendar year. My formula is yielding a result of 0, even though there are 8 hours of LWOP with a date of 2010. These are the formulas I have tried: =SUMIF(Date,"YEAR(Date)=YEAR(TODAY())",LWOP) =SUMIF(Date,IF("Year(Date)=2010",TRUE),LWOP) (I also tried both of those without the quotation marks.) Looking at the posts here, I saw one that seemed similar from last September and I tried this: =SUMIF(Date,YEAR(Date) "=" & YEAR(TODAY()),LWOP) Excel tells me I have an error with that one and won't even let me save the formula. I'm really perplexed. Can anyone advise me how to fix this? (I know I'm going to smack myself in the head when someone tells me what I'm doing wrong.) As always, thanks so much for your help. Try this: =SUMPRODUCT(--(YEAR(Date)=YEAR(TODAY())),LWOP) Also, I suggest NOT using a defined name that is the same as a function name. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SumIf Across Multiple Worksheets - Excel 2003 | Excel Worksheet Functions | |||
SUMIF(S) not available on Excel 2003 | Excel Discussion (Misc queries) | |||
sumif formula in Excel 2003 | New Users to Excel | |||
Sumif for groups and subgroups / Excel 2003 | Excel Worksheet Functions | |||
Validation Confusion-Excel 2003 | Excel Discussion (Misc queries) |