+ Reply to Thread
Results 1 to 5 of 5

Help with SUMIF using a cell as a reference

  1. #1
    Registered User
    Join Date
    06-15-2017
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    6

    Help with SUMIF using a cell as a reference

    I would like the SUMIF to work so that the criteria searches for whatever is in cell C130. This is so if the text changes the formals automatically does too.

    I have tried so many combinations, one being the below, please help!!

    =((SUMIF($A$6:$A$114, "*"&C130&"*",$BF$6:$BF$114 ))/$BF$117)

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,975

    Re: Help with SUMIF using a cell as a reference

    That looks like it should work, though you don't need all those brackets - try it like this:

    =SUMIF($A$6:$A$114, "*"&C130&"*",$BF$6:$BF$114 )/$BF$117

    In what way is it not working for you?

    Pete

  3. #3
    Registered User
    Join Date
    06-15-2017
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Help with SUMIF using a cell as a reference

    Thank you for the brackets help but gave it a go and no luck.

    The problem is it wont give any figure or in this case a percentage.

    Excel SUMIF help.png

    Its odd because the cell above it works which as this formula:

    =SUMIF($A$6:$A$114, "*"&C129&"*",$BF$6:$BF$114 )/$BF$117

    The figures should be the same as in the table above but they are using text specific formals:

    =((SUMIF($A$6:$A$114,"*Cornbury*",BF6:BF114))/$BF$117)

    I wanted to separate the percentages from the numbers in to different tables and make the formulas none text specific as the spreadsheet is going to be much larger with many different ranges.

    Hope that helps, let me know if you need anything else.

    Thanks again,
    Richard
    Last edited by UK_Richie; 06-15-2017 at 07:10 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,975

    Re: Help with SUMIF using a cell as a reference

    Perhaps the "Cornbury" that is in cell C129 has a space after it, in which case it will not match with those in column A.

    You can check for this by using =LEN(C129) in a blank cell somewhere - do you get 8 for that?

    If it is still a problem, then it would help if you attached a sample Excel workbook, rather than a picture of one.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Please note that the Paperclip icon (Attachments button) does not work.

    Hope this helps.

    Pete

    Pete

  5. #5
    Registered User
    Join Date
    06-15-2017
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Help with SUMIF using a cell as a reference

    Hi Pete,

    Thanks for the help, it ended up being a space of all things.

    I have another question, I don't mind starting another thread if that's what I have to do. Problem listed below and spreadsheet should now be uploaded.

    To stop the problem of the range not being copies properly and to make things a little more automatic I want the range column in the breakdown table to automatically update with the ranges from column A but only one like it is at the moment.

    Is this possible?

    Thanks for the help, new to here.

    Many thanks again,
    Richard

+ 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] 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
  2. SUMIF - Reference cell value in a formula?
    By Hugh Alberson in forum Excel General
    Replies: 1
    Last Post: 08-14-2010, 02:46 AM
  3. Excel 2007 : sumif formula using cell reference
    By Pat B in forum Excel General
    Replies: 2
    Last Post: 07-22-2010, 12:05 PM
  4. Sumif with cell reference as criteria
    By sonyap in forum Excel General
    Replies: 4
    Last Post: 03-04-2010, 10:05 PM
  5. Using Cell Reference in SUMIF
    By tklaty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2007, 10:47 PM
  6. I want to use sumif( > a cell reference) and not a value. How?
    By Corie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-05-2006, 03:25 PM

Tags for this Thread

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