+ Reply to Thread
Results 1 to 5 of 5

Help to understand the following formula

  1. #1
    Registered User
    Join Date
    11-28-2011
    Location
    Barcelona
    MS-Off Ver
    Excel 2007
    Posts
    74

    Help to understand the following formula

    I have an excel file with the following formula:

    Please Login or Register  to view this content.
    I understand that we are using the sumifs function to sum different cells according to certain conditions. Nevertheless, I get confused with the "!" operators, and the use of lower case letters and the "'" symbols.

    I learnt about the SUMIFS function, the INDIRECT function, etc. but I can't work out the meaning of this.

    Maybe someone could change this formula in a "human-mode" version?

    I would much appreciate your assistance.
    Last edited by RagonichaFulva; 02-22-2012 at 04:32 PM. Reason: Solved issue

  2. #2
    Registered User
    Join Date
    11-28-2011
    Location
    Barcelona
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: Help to understand the following formula

    Ok. I have re-read it and I understand it.

    It actually calls values from another sheet.

    Now what I ignore is why in the following part (for instance):

    INDIRECT("'"&$J$3&"'!o7:o"&$K$3),
    it uses lower case.

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,161

    Re: Help to understand the following formula

    In the case of cell references, cases dont matter.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Help to understand the following formula

    For better understanding I prefer to show cell references as upper case at all times, if you use a reference in lower case Excel converts it anyway, e.g. type in

    =a1

    and excel will convert that to upper case

    .....but inside INDIRECT "'!k7:k" is simply a text string, not a reference so Excel doesn't convert it within that formula
    Audere est facere

  5. #5
    Registered User
    Join Date
    11-28-2011
    Location
    Barcelona
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: Help to understand the following formula

    Quote Originally Posted by daddylonglegs View Post

    .....but inside INDIRECT "'!k7:k" is simply a text string, not a reference so Excel doesn't convert it within that formula

    Thanks daddylonglegs. Actually, that was what I observed but I didn't figure out that excel treated the reference as a string.

    Now everything makes sense and the world is a fabulous place.

    Thanks to arlu1201 for his explanation, too.

+ 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