| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif (Year)
Hi,
I need to sum a colunm based on the date the cell entered. A1 has a year, 2009 A5:A10000 has Dates, 01/02/2009 E5:E10000 Data to sum E1 the sum of all Column E entered in 2009 Thanks for any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif (Year)
Try
=SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000) HTH Bob "Mike 215" wrote in message ... Hi, I need to sum a colunm based on the date the cell entered. A1 has a year, 2009 A5:A10000 has Dates, 01/02/2009 E5:E10000 Data to sum E1 the sum of all Column E entered in 2009 Thanks for any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif (Year)
On Jan 23, 12:46*pm, "Bob Phillips"
wrote: Try =SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000) HTH Bob "Mike 215" wrote in message ... Hi, I need to sum a colunm based on the date the cell entered. A1 *has a year, * * * * * * *2009 A5:A10000 *has Dates, *01/02/2009 E5:E10000 *Data to sum E1 *the sum of all Column E entered in 2009 Thanks for any help.- Hide quoted text - - Show quoted text - Hi Bob, I did a copy & paste and I typed it in but it is not working, it gives a 0 answer. I am running Office 2003 on XP. Does this matter? Mike |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif (Year)
I think that Bob may have intended to say not
=SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000) but =SUMPRODUCT(--(YEAR(A5:A1000)=A1),E5:E1000) as in his formula the double unary minus would have done nothing. -- David Biddulph "Mike 215" wrote in message ... Hi Bob, I did a copy & paste and I typed it in but it is not working, it gives a 0 answer. I am running Office 2003 on XP. Does this matter? On Jan 23, 12:46 pm, "Bob Phillips" wrote: Try =SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif (Year)
On Jan 23, 1:42*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: I think that Bob may have intended to say not =SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000) but =SUMPRODUCT(--(YEAR(A5:A1000)=A1),E5:E1000) as in his formula the double unary minus would have done nothing. -- David Biddulph "Mike 215" wrote in message ... Hi Bob, I did a copy & paste and I typed it in but it is not working, it gives a 0 answer. I am running Office 2003 on XP. *Does this matter? On Jan 23, 12:46 pm, "Bob Phillips" wrote: Try =SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000)- Hide quoted text - - Show quoted text - David, Your formula works great. I have always been able to get great help here. Thank You Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif with two conditions,current financial year date | Excel Worksheet Functions | |||
| use sumif / Sumproduct for year data | Excel Worksheet Functions | |||
| Sumif by month and year | Excel Worksheet Functions | |||
| Year To Date Sumif question | Excel Discussion (Misc queries) | |||
| sumif for months and year | Excel Worksheet Functions | |||
Linear Mode
