+ Reply to Thread
Results 1 to 11 of 11

How to replace the defined list in sumif(cell range,{defined list},cell range)?

  1. #1
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    252

    Question How to replace the defined list in sumif(cell range,{defined list},cell range)?

    Hi,

    I am doing a spreadsheet to track my expenses by different category and under a category, I am trying to catch the details like "Dine In" will include breakfast, lunch, dinner and supper.
    I'm trying to catch the details from a monthly calendar format and the formula I used for daily calculation is =SUM(SUMIF(B39:B44,{"breakfast","lunch","dinner","supper"},C39:C44))

    This has created a big problem for me. If I were to add or remove an item, example "breakfast", I had to remove 365 times. Is there any way to replace the defined list in {} to just a "Dine In" list so that I do'nt have to change 365 times for the whole year?
    Attached Files Attached Files
    Last edited by Lewis Koh; 07-23-2013 at 11:15 AM.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    why do you wrap everything in a sum formula when you already doing sumif?
    Last edited by humdingaling; 07-22-2013 at 10:38 PM.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    1) Highlight the cells you want to update
    2) Press CTRL-H to open the REPLACE wizard
    3) IN the SEARCH box enter: {"breakfast",
    4) In the REPLACE box, enter: {

    Notice how we included the characters on each side of the string we wanted to update? Then we didn't need the comma any more, so it ended up just being the {
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    Quote Originally Posted by humdingaling View Post
    why do you wrap everything in a sum formula when you already doing sumif?
    click on one of those formulas and then goto Formulas > Evaluate Formula, watch that formula unfold. Now do you understand? Without the SUM(), only the first string would activate. It's a non-CSE array.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    wrap the whole sumif with sumproduct and you can then used named ranges for your list of products

    i did 3 days (in yellow) as examples
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    252

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    Hi humdingaling,

    Thanks this is what I am looking for! :-)
    by the way, is there any limit for the named range? I know there is a limit of cells that we can put as a range (that's why I had so Jan1, Jan2, Jan3., etc), not sure how many more range I can define.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    dont think there would be in the way your using it

    you should probably look into different way of calculating sumif instead of naming range every day though
    your method is not very user friendly to update

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    252

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    Quote Originally Posted by humdingaling View Post
    dont think there would be in the way your using it

    you should probably look into different way of calculating sumif instead of naming range every day though
    your method is not very user friendly to update
    Hmm....Any idea how it will be more user friendly? I wanted the calendar to stay with daily items input.

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    i did something very basic without interfering to what you are doing already too much

    i did rename your 1-31 to be 1st 2nd etc
    reason for that is apparent when you look at the file

    all i did was add some helper columns to the right which you can hide from plain view
    then sumifs based on that column
    seems to work perfectly with reference table sheet
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    252

    Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

    Ah....a bit complicated but I think it's better then selecting the 31 different ranges. Thanks!! :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to Insert defined integer into range defined by variable criteria
    By stereofeedback in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2013, 12:33 PM
  2. [SOLVED] Using cell value to define a range (for a variable defined as a range)
    By Sccye in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-02-2012, 10:25 AM
  3. [SOLVED] Autofill adjacent cell from pre-defined list
    By Seeded_Batch in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 03-23-2012, 06:25 AM
  4. Replies: 3
    Last Post: 07-24-2006, 08:20 PM
  5. Replies: 0
    Last Post: 11-24-2005, 10:29 AM

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