Sum or count date then amount
Hello all,
I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
Sum or count date then amount
Use
=SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100="2/28/2009"),(B1:B100)) or =SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100=Today()),(B1:B100)) Change 100 to your last row You can add other conditions by adding terms like --(C1:C100"2/28/2009") --(C1:C100<"2/28/2009") --(C1:C100=(Today()+7) "pgarcia" wrote: Hello all, I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
Sum or count date then amount
try the Sumproduct
=SUMPRODUCT(--(A1:A10="Susan"),--(C1:C10<DATE(2009,2,28)+7),--(B1:B10)) Change the 2nd part for the day to +6, +2 according. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "pgarcia" wrote: Hello all, I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
Sum or count date then amount
I'm getting 0. Any thoughts? I'm using Excel 2009. I have notice what
SUMPRODUCT does not work as it should in older versions. "Sheeloo" wrote: Use =SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100="2/28/2009"),(B1:B100)) or =SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100=Today()),(B1:B100)) Change 100 to your last row You can add other conditions by adding terms like --(C1:C100"2/28/2009") --(C1:C100<"2/28/2009") --(C1:C100=(Today()+7) "pgarcia" wrote: Hello all, I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
Sum or count date then amount
Hi,
Your question doesn't make sense - C1 = 2/28/2009 is greater than 7? what does that mean? In Excel every date after 1/7/1900 is greater than 7! If you are asking to count or sum the number of items in column C which are more than 7 days old relative to TODAY then there are your formulas: SUM: =SUMPRODUCT(--(A1:A16="Susan"),--(TODAY()-C1:C167),B1:B16) COUNT: =SUMPRODUCT(--(A1:A16="Susan"),--(TODAY()-C1:C167)) You may choose to modify the 7 portion depending on whether you include today in your definition of one day gone. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "pgarcia" wrote: Hello all, I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
Sum or count date then amount
Hi,
Where did you get Excel 2009? Are you testing beta of the next version? The is an Excel 2008 for the Mac but the latest version for the PC is 2007. In all versions, Excel 2009 excluded, SUMPRODUCT works in the same way. If it is returning 0 and you are using the formulas I submitted, then it suggests that the dates may not be legal Excel dates they may be text entries for example. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "pgarcia" wrote: I'm getting 0. Any thoughts? I'm using Excel 2009. I have notice what SUMPRODUCT does not work as it should in older versions. "Sheeloo" wrote: Use =SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100="2/28/2009"),(B1:B100)) or =SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100=Today()),(B1:B100)) Change 100 to your last row You can add other conditions by adding terms like --(C1:C100"2/28/2009") --(C1:C100<"2/28/2009") --(C1:C100=(Today()+7) "pgarcia" wrote: Hello all, I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
Sum or count date then amount
Hi Francis,
No need for the -- in front of the (B1:B10) assuming that the contents of column B are numbers and not numbers entered as text. And if they are number then you can drop the parens. If they are text numbers then --B1:B10 is sufficient. The general reason for using -- is to convert the results of a text from {TRUE,FALSE,TRUE...} to {1,0,1,...} so that calculations can be performed. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "francis" wrote: try the Sumproduct =SUMPRODUCT(--(A1:A10="Susan"),--(C1:C10<DATE(2009,2,28)+7),--(B1:B10)) Change the 2nd part for the day to +6, +2 according. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "pgarcia" wrote: Hello all, I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009 is greater then 7 days. I would need to also do the following: 7 or more days (which is the example), 3-6 days old and 1-2 days old. Thanks |
All times are GMT +1. The time now is 05:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com