| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using SUMIF for a financial value AND a DATE
Hi, I'm trying to use a SUMIF formula to add up 2 criteria.
1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- .... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
=SUMPRODUCT((A2:A5C$1)*(A2:A5)*(B2:B5<D$1))
if your data table is in columns A and B, and your £5 criterion in C1 and the 1/2/2008 date criterion in D1. -- David Biddulph "spudsnruf" wrote in message ... Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
Try one of these:
If there might be empty date cells: D1 = 1/2/2008 =SUMPRODUCT(--(A1:A45),--(ISNUMBER(B1:B4)),--(B1:B4<D1),A1:A4) If there won't be empty date cells: =SUMPRODUCT(--(A1:A45),--(B1:B4<D1),A1:A4) -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
Thanks very much. In the end I went with the vesion that fred smith sent
after I'd explained it a little better. Thank you for your answer anyway. "T. Valko" wrote: Try one of these: If there might be empty date cells: D1 = 1/2/2008 =SUMPRODUCT(--(A1:A45),--(ISNUMBER(B1:B4)),--(B1:B4<D1),A1:A4) If there won't be empty date cells: =SUMPRODUCT(--(A1:A45),--(B1:B4<D1),A1:A4) -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
How about:
=SUMPRODUCT(--(B1:B4<DATE(2008,1,2))*(A1:A45)*(A1:A4)) -- Gary''s Student - gsnu200769 "spudsnruf" wrote: Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
Hi, still wrestling with this one. I think I need to explain it more
thoroughly. WORKSHEET 1 A B 1 Mike =SUMIF('Costs detail'!$D$2:$D$502,B29,'Costs detail'!$L$2:$L$502) So in cell B1, I am currently looking in the 2nd Worksheet called Costs detail, and looking for all entries for Mike (in cells D2 to D502) and returning the amount earned (in cells L2 to 502) But now what I need to do is change the formula in B1, so that it also looks in to WORKSHEET 2 (the costs detail sheet) and returns only values for dates (in col G) that are before the date of 31/1/2008, relating to Mike. Hope this helps. Its not rocket science. But it may as well be sometimes. Thank you "Gary''s Student" wrote: How about: =SUMPRODUCT(--(B1:B4<DATE(2008,1,2))*(A1:A45)*(A1:A4)) -- Gary''s Student - gsnu200769 "spudsnruf" wrote: Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
When you have more than one criteria, you need to use Sumproduct rather than
Sumif. So you want: =sumproduct(--('Costs detail'!$D$2:$D$502=B29),--('Costs detail'!$G$2:$G$502<date(2008,1,31)),'Costs detail'!$L$2:$L$502) Regards, Fred "spudsnruf" wrote in message ... Hi, still wrestling with this one. I think I need to explain it more thoroughly. WORKSHEET 1 A B 1 Mike =SUMIF('Costs detail'!$D$2:$D$502,B29,'Costs detail'!$L$2:$L$502) So in cell B1, I am currently looking in the 2nd Worksheet called Costs detail, and looking for all entries for Mike (in cells D2 to D502) and returning the amount earned (in cells L2 to 502) But now what I need to do is change the formula in B1, so that it also looks in to WORKSHEET 2 (the costs detail sheet) and returns only values for dates (in col G) that are before the date of 31/1/2008, relating to Mike. Hope this helps. Its not rocket science. But it may as well be sometimes. Thank you "Gary''s Student" wrote: How about: =SUMPRODUCT(--(B1:B4<DATE(2008,1,2))*(A1:A45)*(A1:A4)) -- Gary''s Student - gsnu200769 "spudsnruf" wrote: Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
Thanks a lot, Fred. I had the same problem as spudsnruf, and you solved it!
"Fred Smith" skrev i melding ... When you have more than one criteria, you need to use Sumproduct rather than Sumif. So you want: =sumproduct(--('Costs detail'!$D$2:$D$502=B29),--('Costs detail'!$G$2:$G$502<date(2008,1,31)),'Costs detail'!$L$2:$L$502) Regards, Fred |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
Great. Thanks for the feedback.
Fred. "Obonden" <kjeblo(æt)frisurf.no wrote in message ... Thanks a lot, Fred. I had the same problem as spudsnruf, and you solved it! "Fred Smith" skrev i melding ... When you have more than one criteria, you need to use Sumproduct rather than Sumif. So you want: =sumproduct(--('Costs detail'!$D$2:$D$502=B29),--('Costs detail'!$G$2:$G$502<date(2008,1,31)),'Costs detail'!$L$2:$L$502) Regards, Fred |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using SUMIF for a financial value AND a DATE
Fred, worked perfectly. Brilliant and thanks so much.
"Fred Smith" wrote: When you have more than one criteria, you need to use Sumproduct rather than Sumif. So you want: =sumproduct(--('Costs detail'!$D$2:$D$502=B29),--('Costs detail'!$G$2:$G$502<date(2008,1,31)),'Costs detail'!$L$2:$L$502) Regards, Fred "spudsnruf" wrote in message ... Hi, still wrestling with this one. I think I need to explain it more thoroughly. WORKSHEET 1 A B 1 Mike =SUMIF('Costs detail'!$D$2:$D$502,B29,'Costs detail'!$L$2:$L$502) So in cell B1, I am currently looking in the 2nd Worksheet called Costs detail, and looking for all entries for Mike (in cells D2 to D502) and returning the amount earned (in cells L2 to 502) But now what I need to do is change the formula in B1, so that it also looks in to WORKSHEET 2 (the costs detail sheet) and returns only values for dates (in col G) that are before the date of 31/1/2008, relating to Mike. Hope this helps. Its not rocket science. But it may as well be sometimes. Thank you "Gary''s Student" wrote: How about: =SUMPRODUCT(--(B1:B4<DATE(2008,1,2))*(A1:A45)*(A1:A4)) -- Gary''s Student - gsnu200769 "spudsnruf" wrote: Hi, I'm trying to use a SUMIF formula to add up 2 criteria. 1) financial value above £5 AND 2) before a date of 1/2/2008 Using a simple table example.... £10 2/3/2008 £5 12/1/2008 £8 10/12/2007 £12 1/1/2007 I would be looking for an formula that gave me £20. I think its something to do with ... =sumif(-- ... type equation, but I cant work out exactly what it is , or how I do the BEFORE DATE OF 1/2/2008 bit. Thank you very much |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert date to financial year format 2006-07 | Excel Worksheet Functions | |||
| How to compare a date with financial year | Excel Discussion (Misc queries) | |||
| SUMIF for date | Excel Discussion (Misc queries) | |||
| SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
| sumif based on date | Excel Worksheet Functions | |||
Hybrid Mode
