# Thread: sum/sumif (2000)

1. ## 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))

2. ## Re: sum/sumif (2000)

What are the actual contents of A2:A367 in Workbook One? 1, 2, 3, 4, ... or something else?

3. ## 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.

4. ## Re: sum/sumif (2000)

If you select cell A2 in Workbook one, what do you see in the formula bar?

5. ## Re: sum/sumif (2000)

1/1/1968, formatted as mm/dd. I chose 1968 arbitrarily for my year in column A.

6. ## Re: sum/sumif (2000)

Thanks, that is useful. Now, the same question for cell A2 in Workbook Two. Thanks in advance.

7. ## 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.

8. ## 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.

9. ## 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.

10. ## 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.

11. ## Re: sum/sumif (2000)

Never mind, the second one worked as an array formula...

12. ## Re: sum/sumif (2000)

Here is a demo workbook using SUMPRODUCT.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•