Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    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))

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum/sumif (2000)

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

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum/sumif (2000)

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

  5. #5
    Bronze Lounger
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum/sumif (2000)

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

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

  11. #11
    Bronze Lounger
    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...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •