Posts by cya

Greetings, I am trying to find a way to add up multiple occurrences of numbers within a column of cells. In some cells there may be more than one occurrence and I would like to count all of them within the cell. Also I would like to use strings like "2-5" and the code be able to read search for this also. At present I am using the Countif function, but I am pretty sure that that doesn't actually count the number of occurrences within the cell. I am attaching a sample. Column V are the cells of interest. My formulas are found in V152:V164. Thank you so much.

Perfect. Thank you so much!!

Sorry, my estimations or counting is found in Y62 for the purple & bold, Y65 for the double underline, and Y11 for the single underline.

Document uploaded.

Explanation:
Column Y is the one I am interested in.

1. I would like to count how many times the cells (Y3:Y46) have text which has a single underline. It would be really cool if there was a way to determine even if there were breaks in the underline.For instance Y28 has "&" and then later in the same cell "lo spirito" and this would count as 2. However I would be fine with just something that counted every cell which included something underlined. If I counted right the total would be 11.
2. Same thing for double underlined.
3. Count how many cells include a text which is the color purple (color code 10498160) and Bold at the same time. As you can see there is other bold text and other purple text, so it has to read that the text is both bold in purple in the same place.

I hope this is somewhat clearer.

I appreciate so much your help!! Unfortunately I think I wasn't clear enough. In the end I would like to have a count of all cells which contain some or any text that is underlined once, then another separate count of all cells which contain some or any text underlined twice. So I am trying to find a formula that will count the single underline in a cell whether it has the double underline or not. Sorry for the confusion, I hope that is clear, and again Thank you!

Thank you, that does confirm the suspicion I had for the three categories, but it still doesn't recognize cells which have both double and single underlines. It ignores them completely, whereas I would like it to still count them.

Even when changing the style on this it for some reason doesn't seem to work right. It might count cells that only has single underline whereas I have some with both single and double in the same cell.

I did find this function for counting cells with underline in them, but it appears to count just underline and not specify single or double.

Function CountUnder(rngInput As Range) As Double
Application.Volatile

Dim rngCell As Range
Dim lngCountUnder As Long
lngCountUnder = 0

For Each rngCell In rngInput
If rngCell.Font.Underline <> xlUnderlineStyleNone Then
lngCountUnder = lngCountUnder + 1
End If
Next rngCell

CountUnder = lngCountUnder

End Function

I am looking for a couple formulas, possibly VBA.
1. To count all cells in a column which has double underlined text.
2. To count all cells in a column which has text both in a certain color (purple 10498160) and bold. It cannot just find bold, it must be both bold and that color in the same text, because other words might be in bold in the same cell, but not purple.

Thank you for whatever help you can offer.

I think that will work. Thank you so much!

Carim, thank you so much for being willing to help. Unfortunately, I think what you are suggesting is what I already have. What I am looking for is a way to fill in Column B, based on the information found in Column D. In the sample I simply filled them in manually, but as there are hundreds of chapters, this is a bit laborious.

Sorry, here is the file.

The problem: I have a file which has Name of the book (Column A), Chapter number (Column B), colon (Column C). verse number (Column D) and the text (Column E). All of these are then combined in Column F for the final results. The problem is inserting the chapter numbers.
Possibility one: Most of the time I have the beginning chapter number which appears in Column D, before the next chapter. If it is possible to read the new number in Column D and fill in the following cells in Column B until the next change of number in Column D.
Possibility two: Read the change in verse number from Column E when it starts over at 1 and change the chapter number up by 1 filling in the cells of Column B with that new number until the next number 1 comes up in Column D.

Thank you.

Re: sum of following 5 cells only if it is a number of 0 or greater

This attachment is more specific. You will see the highlighted cells are where the customer first started with the company, For instance customer in Row 15 started just two months before the end of year data messes up the formula. The second sheet gives those two columns a value of -.05 that way it will not add them. I guess really it could add the 6 cells in the row to follow or it could use the value in the sixth cell of sheet 2 since sheet 2 keeps a running total, but either way it has to skip the two columns between the years.

thank you,
Cya

Re: Find the first column with sum of &lt;1 then add the previous six cells.

okay maybe this attachment and example will help.

The highlighted yellow column AD represents the month after the current month. The cell the formula will fit into is cell H13-150. and I guess I must add the column <1 must come after a column >1 so as not to confuse the formula and choose the very first month if blank.
Example.
Customer Row 19. The formula first recognizes that column N in this case is >0 and so it starts looking for the next Column that is <1. It sees that Column AD <1 from customer rows 13-150. The formula then adds the previous 6 cells in row 19 and divides by 6. The only hitch is the two rows between the sets of 12 months. Because of this I have made a second sheet and those two rows values are -.05 Therefor the formula would actually have to read the previous six rows with a value >-.01

I hope this all makes sense. If something is not right in the asking or explanation please let me know, or maybe a better way I could set this up.

much thanks
cya

Re: Find the first cell in a column &gt;0 and last column &lt;1 and count the rows between

Okay I hope this looks more relevant, and I will do my best to explain.

The hightlighted column represents the month after the current month. Below the month names you will notice column AD= 0. I want to take the total amount a customer has paid, and divide by the total months they have been a customer.
Example:
Customer Line 16 started with us in February cell 016. This customer is consistent but most are not. The amount may be 0 in one cell after they have started. I want to add the total from Cell O16 to the current month AC16. Then I want the formula to count the cells between the start date(o16) and the current month(AC16) and divide by that number. The answer in this case is $65 because they have been consistent with $65 every month. Customer line 19 however started at a higher amount but they missed February (AC19) of the second year.

I hope this helps explain it a little better. Thank you for helping me.

cya

I am trying to average out customers bills per month. I need to take the first month they started paying to the current month. Then count the months between and divide by that so that I can get the average. The only hiccup is that my layout has a set of statistics including two columns after every year.

Much thanks,
cya

Basically just that.

I need to find the last column with a value greater than 0, or the first column with a value of <1.
Then add the previous six cells and divide by 6.

Much thanks,

cya

I am trying to use the formula below which successfully gives me the first cell with a value great than 0 then adds the next 5 and divides by 6 to give me an average. Unfortunately the way my layout is I have two cells after 12 months and I need the formula to recognize that if those two cells are part of the next five it skips those and only counts the next five cells with a 0 or positive value. I have made a second sheet and put the values from the first copied over. The two columns between the years in sheet 2 have a value of -.05 Any ideas.

=IF(D13>0,(SUM(INDEX(N13:HH13,MATCH(TRUE,N13:HH13>0,0)):INDEX(N13:HH13,MATCH(TRUE,N13:HH13>0,0)+5)))/6, 0)

I have attached a sample but basically it is the above formula only with the +5 a conditional statement if the cell >-.01
Much thanks,

cya

Re: Sum columnA/sum columnB, if row Has data up to columnB

converteds,

Okay so I took some time to do it now. It looks like it is going to work, and this might help me with some of my other problems as well. After making the second page my new formula looks like this

=((SUM(T13:T25))/(SUM(IF(Sheet2!T2:Sheet2!T25>0,$M$13:$M$25,0))))

It is an array formula. I may have gone heavy on the () but I always add a few extra just in case.
At least it appears to work and does it great. Now I just have to apply this new fun working with two worksheets into my other questions.

Thank you again for your help,
cya