Results 1 to 12 of 12
Thread: sum/sumif (2000)

20051012, 10:55 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
sum/sumif (2000)
Workbook one contains data by day (in rows), and by years (in columns, 30 in total). What I would like to do is summarize this data by month and year in another worksheet, in the same format, except that instead of each day listed in each row, only the months would be listed. The columns in workbook two would be the same as those in workbook one (i. e., years). I have tried the following, but it doesn't work. Any ideas?
=SUM(IF(AND(MONTH(Workbook One'!$A$2:$A$367)=MONTH($A2),YEAR(Workbook One'!$B$1:$AO$1)=YEAR(B$1)),Workbook One'!$B$2:$B$367))

20051012, 11:06 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum/sumif (2000)
What are the actual contents of A2:A367 in Workbook One? 1, 2, 3, 4, ... or something else?

20051012, 11:09 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum/sumif (2000)
Sorry, Hans. Workbook one contains the data I want to summarize in Workbook two. The range A2:A367 corresponds to the days of the year, while the range B2:B367 is the range of data for the year that is set out in B1.

20051012, 11:11 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum/sumif (2000)
If you select cell A2 in Workbook one, what do you see in the formula bar?

20051012, 11:18 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum/sumif (2000)
1/1/1968, formatted as mm/dd. I chose 1968 arbitrarily for my year in column A.

20051012, 11:30 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum/sumif (2000)
Thanks, that is useful. Now, the same question for cell A2 in Workbook Two. Thanks in advance.

20051012, 11:31 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum/sumif (2000)
1/1/2005, formatted in mmmm; A3 is 2/1/2005, again formatted in mmmm, etc., down the column to December.

20051012, 11:44 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum/sumif (2000)
Try this formula in cell B2:
<code>
=SUMPRODUCT((MONTH('Workbook One'!$A$2:$A$367)=MONTH($A2))*'Workbook One'!B$2:B$367)
</code>
Note the use of absolute and relative references.
Fill down to B13, then fill right to AE13.

20051012, 11:53 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum/sumif (2000)
Alternatively, enter this formula in B2 as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter:
<code>
=SUM(IF(MONTH('Workbook One'!$A$2:$A$367)=MONTH($A2),'Workbook One'!B$2:B$367))
</code>
and fill down then right.

20051012, 11:59 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum/sumif (2000)
Hans,
When I try your first suggestion, I get a #value, and when I try your second, the value it return is the sum of the data for the whole year.

20051012, 12:01 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum/sumif (2000)
Never mind, the second one worked as an array formula...

20051012, 12:12 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum/sumif (2000)
Here is a demo workbook using SUMPRODUCT.