+ Reply to Thread
Results 1 to 3 of 3

Returning the sum of a range based on specified criteria

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    87

    Returning the sum of a range based on specified criteria

    Hi all - Please find the attached sample workbook. What I'd like is for the user to select a month in C2 on the 'Report' sheet and to have cells c4:c8 reflect the sum for the corresponding month of the categories starting with the letters in the cells in b4:b8.

    I realize that the easiest way to do this is to create a few more rows on the 'Date' sheet and to use a sumif formula (sumif($b$2:$b$68,"RU"&"*",c$2:c$68) for categoies starting with RU in January, fo example), and then do a vlookup on the Reports page, but I'd like to do away with creating more rows in the 'Date' sheet if possible.

    I'm thinking that one way would be to utilize sumif, with the range rows pre-defined but the columns based on the month.
    =SUMIF('Data'!$B$2:$B$68,"RU"&"*",'Data'!"<row based on month>"&"$2:<row based on month>"&"$68)
    What I'd need is to make this work is a formula which would look up the month's name in cell c2 on the 'report' sheet and return the column letter of that month in the 'data' sheet.

    If this is too complicated, I'm also open to other, simpler ideas as to how to accomplish this! Thank you.

    Adam
    Attached Files Attached Files
    Last edited by hektisk; 04-03-2011 at 06:37 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Returning the sum of a range based on specified criteria

    Your Sumif formula was close, see attachment.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-13-2011
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    87

    Re: Returning the sum of a range based on specified criteria

    Quote Originally Posted by WHER View Post
    Your Sumif formula was close, see attachment.
    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1