If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMIF for Dates / Months ?
Hi, I have a spreadsheet that has every day of the year (1/1/2004 to
12/31/2004) and I am trying to sum a column next to it to get only totals for each month. Basically I want to know what I get as a sum for all of January, February, etc. So I am trying to use SUMIF in combination with Month(A1:A365)=1 through =12 and just sum those that are in month 1 and then those in month 2, etc. Column A has all dates and column B has revenue numbers ($ made that day) Can anyone suggest how to do it right? I was trying arrays like: for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))} I also tried SUMIF such as: example February: =SUMIF(A:A,MONTH(A:A)&""&"2",B:B) I know I am doing something utterly wrong but can you please help? Thanks! |
#2
|
|||
|
|||
One way:
=SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365) Hope this helps Rowan "ZMAN" wrote: Hi, I have a spreadsheet that has every day of the year (1/1/2004 to 12/31/2004) and I am trying to sum a column next to it to get only totals for each month. Basically I want to know what I get as a sum for all of January, February, etc. So I am trying to use SUMIF in combination with Month(A1:A365)=1 through =12 and just sum those that are in month 1 and then those in month 2, etc. Column A has all dates and column B has revenue numbers ($ made that day) Can anyone suggest how to do it right? I was trying arrays like: for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))} I also tried SUMIF such as: example February: =SUMIF(A:A,MONTH(A:A)&""&"2",B:B) I know I am doing something utterly wrong but can you please help? Thanks! |
#3
|
|||
|
|||
Hi!
Try this: Make a list of the month names: Jan Feb Mar ... Dec Assume the dates are in column A, A1:A365 with no empty cells within the range. In the cell beside JAN enter this formula and copy down to DEC: =SUMPRODUCT(--(MONTH(A$1:A$365)=ROW(1:1)),B$1:B$365) Biff "ZMAN" wrote in message oups.com... Hi, I have a spreadsheet that has every day of the year (1/1/2004 to 12/31/2004) and I am trying to sum a column next to it to get only totals for each month. Basically I want to know what I get as a sum for all of January, February, etc. So I am trying to use SUMIF in combination with Month(A1:A365)=1 through =12 and just sum those that are in month 1 and then those in month 2, etc. Column A has all dates and column B has revenue numbers ($ made that day) Can anyone suggest how to do it right? I was trying arrays like: for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))} I also tried SUMIF such as: example February: =SUMIF(A:A,MONTH(A:A)&""&"2",B:B) I know I am doing something utterly wrong but can you please help? Thanks! |
#4
|
|||
|
|||
Hi, Try, =SUMPRODUCT(--(MONTH($A$1:$A$500)=1),$B$1:$B$500) Adjust your range. Full column reference won't accept in SUMPRODUCT. HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=398862 |
#5
|
|||
|
|||
Fill in the first day dates involving the months of the year of interest
in column C from C1 on: 1-Jan-04 1-Feb-04 1-Mar-04 etc. In D1 enter & copy down: =EOMONTH(C1,0) In E1 enter & copy down: =SUMIF(A:A,"="&C1,B:B)-SUMIF(A:A,""&D1,B:B) ZMAN wrote: Hi, I have a spreadsheet that has every day of the year (1/1/2004 to 12/31/2004) and I am trying to sum a column next to it to get only totals for each month. Basically I want to know what I get as a sum for all of January, February, etc. So I am trying to use SUMIF in combination with Month(A1:A365)=1 through =12 and just sum those that are in month 1 and then those in month 2, etc. Column A has all dates and column B has revenue numbers ($ made that day) Can anyone suggest how to do it right? I was trying arrays like: for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))} I also tried SUMIF such as: example February: =SUMIF(A:A,MONTH(A:A)&""&"2",B:B) I know I am doing something utterly wrong but can you please help? Thanks! |
#6
|
|||
|
|||
Hi, what is "--" before the MONTH function? Do I enter it just like
that? Also, is this an array or a regular function? Finally, I tried it every way and it won't work - gives either a 0 or a #NUM! error. Rowan wrote: One way: =SUMPRODUCT(--(MONTH(A1:A365)=2),B1:B365) Hope this helps Rowan "ZMAN" wrote: Hi, I have a spreadsheet that has every day of the year (1/1/2004 to 12/31/2004) and I am trying to sum a column next to it to get only totals for each month. Basically I want to know what I get as a sum for all of January, February, etc. So I am trying to use SUMIF in combination with Month(A1:A365)=1 through =12 and just sum those that are in month 1 and then those in month 2, etc. Column A has all dates and column B has revenue numbers ($ made that day) Can anyone suggest how to do it right? I was trying arrays like: for February: {=SUM(IF((MONTH(A:A)=2),1,0)*(B:B))} I also tried SUMIF such as: example February: =SUMIF(A:A,MONTH(A:A)&""&"2",B:B) I know I am doing something utterly wrong but can you please help? Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Why "datedif" function results sometimes negative numbers? | Ambrosiy | Worksheet Functions | 1 | July 8th, 2005 11:29 AM |
How to use SUMIF to return sums between two values located in cells | ScottBerger | Worksheet Functions | 1 | November 18th, 2004 07:09 PM |
Fiscal Year Calculations | Loretta | Worksheet Functions | 5 | August 19th, 2004 09:05 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |