Function to sum until bold

Hi!

Ive been trying to find a solution to this but cant seem to find it. Can I create a function that will sum until it hits another bold cell? I have a header column that sums down all the values in its category and displays the total in bold. (There are multiple categories each having a different varying number of rows) I do it manually now but I'd like to do it automatically to just sum the numbers in that column until it reaches a bold number. Can anyone help me or tell me where to start?

There are no conventional methods that would achieve this.

While it is theoretically possible to do, it would be borderline impossible without seeing the layout of your sheet.

Are there any other factors that could be used as an identifier?

From your description of your problem, it sounds like you need SUMIF() with category as criteria. A sample sheet would help.

Im not sure what you mean if there are other factors. i created a function in vba that "sums if bold"(cell H16, it doesnt work because i copied it to a new sheet) to get the totals. but now i want to automatically create those bold totals by summing down until it hits a new category, or bold numberSample Sheet.xlsx

Originally Posted by biancam

Im not sure what you mean if there are other factors.

Anything else in the bold rows that could be used to identify them, in your sample there is, only the rows you want to sum have 1 entered in column A, so you could use a conventional formula.

=SUMIF($A:$A,1,H:H)

Its not working for me, the 1's will be different, there really is no other factors as an identifier, it will be different every time.

I would like cell H7 (the Material Only Total box ) to sum cells H8:H10 (which will be a different number of rows each time) and will be bolded. since there are no identifying factors i thought it could be identified by whether the number was bold or not.

Hope this makes sense.

Going back to your earlier post

Originally Posted by biancam

i created a function in vba that "sums if bold"(cell H16, it doesnt work because i copied it to a new sheet)

Do you mean that the formula is in a different sheet to the bold cells, of that you're copying everything to a new workbook that doesn't have the code?

Please post the code, it was missing from your sample workbook.

That formula is in the same sheet summing those bold cells Im trying to make now.

I didnt post it because I don't think its really relevant to what I'm trying to do but here it is:


Function SumIfBold(MyRange As Range) As Double

Dim cell As Range
For Each cell In MyRange
If cell.Font.Bold = True Then
SumIfBold = SumIfBold + cell
End If
Next cell

End Function

So why don't you just add the code to the new workbook?

You're not going to be able to this without vba unless you can find a common value or identifier in the rows, standard excel functions cannot identify cell formats.

Im not sure you understand what Im trying to do. The function i posted before was to sum the cells that are bold. right now, in a completely different cell im trying to sum the cells below it until it reaches a bold cell. using vba is fine I just dont know how to write it, I thought i would use an offset but im not sure.

You're right, I was missing the point of your question.

Without vba, enter this formula into H7 and Array confirm with Shift Ctrl Enter, then copy the cell, not the formula, and paste as required.

=SUM(H8:INDEX(H8:H$1000,MATCH(TRUE,ISBLANK($F8:$F$1000),0)-1))

Naturally, with vba the formula will be less complex, as soon as I have it working I'll post the code.

I have no idea what you did, but it works!!! this is perfect, thanks

As promised, the vba version

The code

Please Login or Register  to view this content.

The formula

=SumNotBold(H:H)

You don't need to enter rows in the formula, the function will find them.

Looks good, i cant get the code to work correctly though. it keeps giving me #VALUE! I dont have to select a range? because I have merged cells and words further down in the sheet. Im not sure what it could be

You need to enter a range as columns, i.e. H:H

You don't need to include rows in the range, but the function should accept it if you want to test with a smaller range.

The text will not cause problems unless it's between the cell with the formula and the next cell with a bold font.

Merged cells will cause problems, I'll edit the to ignore them.

Can you post a sample sheet with merged cells and text to match your main sheet layout, the only time I get an error is when there is nothing to sum below the cell with the formula.

I cant change H16 and J16 to those manual divisions because I usually have more than two items. in the sample sheet when i get rid of sum if bold it works. when i leave it in it gives me a circular reference.

when i copied the code to the whole workbook it works but only for one location. Im attaching the whole sheet. I hope this will be more helpful

It's the formula that return blanks causing the problems, this fixes it.

Please Login or Register  to view this content.

WORKS!!! thanks so much

That works too! I think the vba is a little cleaner, since I'm not the only one using the sheet but is there any other advantages to not using vba?

Cool, thanks everyone!

Hi,

I tried this code and it worked out perfectly.
I wonder how it can sum above the active cell.
I did some changes with no success.

Best Regards,

Hi,

im new here.

Can anyone help to get this sample file to sum up just the blue rows.

I've used autosum to get the first cell [H2] but I would like to get a formula going [not vba] to automate this for future use.

Autosum requires me to manually select the cells hence the request.

I have tried to implement what has been suggested already on this thread but my excel skills are terrible.

Please advise.

Thanks in advance.

Hi faqir41.

Unfortunately I can't answer your question for you here because your post does not comply with the forum rules.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.

In addition to being a requirement of the rules, a new thread (when appropriate) will get more attention from people than an old thread so you're more likely to get your question answered.

I would suggest a quick read of the forum rules before posting.