Help with SUMIF using a cell as a reference

**UK_Richie** · 06-15-2017, 06:38 PM

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)

**Pete_UK** · 06-15-2017, 06:44 PM

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

**UK_Richie** · 06-15-2017, 07:08 PM

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

**Pete_UK** · 06-15-2017, 07:24 PM

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

**UK_Richie** · 06-16-2017, 06:18 PM

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