+ Reply to Thread
Results 1 to 7 of 7

SumIf and dinamic range name...help

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Rome, Italy
    MS-Off Ver
    2011
    Posts
    3

    SumIf and dinamic range name...help

    Hello guys,
    this is my first post on this forum, I tryed a little search but I have not found the answer to my question. If I am wrong, please don't esitate to forward me to the right post or link.
    Well, imagine to have a 13 column sheet where in A there is a simple description and in the subsequents column months from January to December.
    I need to sum a specific description for a specific month that I need to select.
    So, table could be like this:

    Type January February
    Cable 1.000€ 1.500€
    Plug 500€ 250€
    Cable 100€ 150E

    I defined a named range called "February" and then a formula in A1000

    A1000: =sumif(A1:Z300;"Cable";February)

    and it does work! The result is 1.650€.

    I would like to select the month under analisys and then I write in cell A999 the name of the named range previously defined:
    A999: February
    and then chanded A1000 in: =sumif(A1:Z300;"Cable";A999)
    but unfortunatly it doesn't work...

    How can I do to be able to select one of the named range previously defined and get the right sum?
    thanks to all, I'll wait for your suggestion.
    I hope to have well explained what I am looking for.
    ciao
    fausto

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: SumIf and dinamic range name...help

    Possibly:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-12-2014
    Location
    Rome, Italy
    MS-Off Ver
    2011
    Posts
    3

    Re: SumIf and dinamic range name...help

    Quote Originally Posted by TMS View Post
    Possibly:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    GREAT!!!
    It is exactly what I Was looking for!!
    i Will post it on italian forum so other people can Also use it.
    Thank you very much Dear TMS, hope to meet you againg.
    Ciao
    Fausto

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: SumIf and dinamic range name...help

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SumIf and dinamic range name...help

    If the text is only in column A I would always advise you to use that column explicitly, e.g.

    =SUMIF(A1:A300;"Cable";February)

    If you use the other version then if "Cable" can only exist in column A the results will always be the same (but you are checking 25 columns that you don't need to check), but if "Cable" should exist in E10 for example then the formula can sum numbers outside the February range.

    Also I believe that SUMIF with mismatched range sizes is a volatile formula to some extent so it may increase your calculation times unnecessarily.
    Audere est facere

  6. #6
    Registered User
    Join Date
    08-12-2014
    Location
    Rome, Italy
    MS-Off Ver
    2011
    Posts
    3

    Re: SumIf and dinamic range name...help

    Thanks daddylonglegs (daddy is the nckname with which we usually call our son
    You're right, The range in which I search the string "cable" is restricted to A2:A500, there is no need to search it on the other columns. Thanks for your advice,
    Best regards
    Fausto

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SumIf and dinamic range name...help

    Quote Originally Posted by fcasini View Post
    ......daddy is the nckname with which we usually call our son
    Hey Fausto!

    You've got this the wrong way round, he should be calling you daddy!

+ 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. [SOLVED] Copy range of cells to another file (dinamic name)
    By mirenoba in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-12-2014, 06:40 AM
  2. excel dinamic table
    By bernie777 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2014, 09:48 AM
  3. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  4. [SOLVED] dinamic path name
    By emilija in forum Excel General
    Replies: 3
    Last Post: 05-08-2006, 01:10 PM
  5. Determine a dinamic range
    By BittyBite in forum Excel General
    Replies: 0
    Last Post: 02-24-2005, 08:24 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