Summing one column based on date in another column
I desperately need help here. I have a spreadsheet that has sales price in
one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
For this example I'll assume date is in column A and amount is in column B:
If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
Thank you so much for the quick reply. I did this with the seperate column
showing January for example. The problem is I have dozens of calculations to do by month so I would have to add 20 or 30 columns to do this. I am trying to get it to count january 5, 2005 and january 6, 2005 as all being in Jan. I'll try what you just sent me and see if it works. "Elkar" wrote: For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
Thank you! Thank you! Thank you! I cannot thank you enough. I have been
trying to figure this out for 4 days, as I didn't know this forum existed. You made my day:) "Elkar" wrote: For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
New problem. When I use this formula it is counting the dollars in the date
columns that are blank. Do you know how I would fix this? "Elkar" wrote: For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
Ah, yes, that would be a problem when searching for January since a blank
cell assumes month 1. Try this: =SUMPRODUCT(D1:D8,--(MONTH(A1:A8)=1),--(A1:A8<"")) This will add the additional condition of not being blank. HTH, Elkar "excel guru i''m not" wrote: New problem. When I use this formula it is counting the dollars in the date columns that are blank. Do you know how I would fix this? "Elkar" wrote: For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
That works perfect. Thanks again!
"Elkar" wrote: Ah, yes, that would be a problem when searching for January since a blank cell assumes month 1. Try this: =SUMPRODUCT(D1:D8,--(MONTH(A1:A8)=1),--(A1:A8<"")) This will add the additional condition of not being blank. HTH, Elkar "excel guru i''m not" wrote: New problem. When I use this formula it is counting the dollars in the date columns that are blank. Do you know how I would fix this? "Elkar" wrote: For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
Summing one column based on date in another column
Sorry, I meant to put this:
=SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1),--(A1:A100<"")) I got some ranges mixed up with another formula I was working on. "Elkar" wrote: Ah, yes, that would be a problem when searching for January since a blank cell assumes month 1. Try this: =SUMPRODUCT(D1:D8,--(MONTH(A1:A8)=1),--(A1:A8<"")) This will add the additional condition of not being blank. HTH, Elkar "excel guru i''m not" wrote: New problem. When I use this formula it is counting the dollars in the date columns that are blank. Do you know how I would fix this? "Elkar" wrote: For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being summed, you could place the number of the month in a seperate cell (we'll say C1 for example) and use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=C1)) HTH, Elkar "excel guru i'm not" wrote: I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. |
All times are GMT +1. The time now is 04:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com