Finding the Sum of Numbers between words in the entire workbook.

[aimatarv]

15426


Hi Guys and Gals,
I'm having a lot of trouble with this one. So much trouble that I've decided to create an account to a forum.

For one I do not know if you're able to see the image I attached properly.

1. The cells I have highlighted in YELLOW is supposed to be the SUM of the GREEN cells above. So the first YELLOW cell would equal: $149.56. This YELLOW cell would always be located to the right of the cell named: TOTALS.

2. 2 cells down from the yellow, or Column F, will display the SUM of yellow and the RED cell.


The cell colors green, yellow and the blue in Column F were only added for visual assistance. They are not apart of the real spreadsheet.

[SamT]

Your words do not match the image.

According to the image
Formula in C13 is =Subtotal(9,C11:C12)
Formula in C19 is =Subtotal(9,C15:C18)

Formula in F13 is =C13+E13
Formula in F19 is =C19+E19
The formulas in F can be copied and pasted any where in F


Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Syntax
SUBTOTAL(function_num,ref1,ref2,...)
Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.


Function_Num
Function


1
AVERAGE


2
COUNT


3
COUNTA


4
MAX


5
MIN


6
PRODUCT


7
STDEV


8
STDEVP


9
SUM


10
VAR


11
VARP

[aimatarv]

15426


Hi Guys and Gals,
I'm having a lot of trouble with this one. So much trouble that I've decided to create an account to a forum.

For one I do not know if you're able to see the image I attached properly.

1. The cells I have highlighted in YELLOW is supposed to be the SUM of the GREEN cells above. So the first YELLOW cell would equal: $149.56. This YELLOW cell would always be located to the right of the cell named: TOTALS.

2. 2 cells down from the yellow, or Column F, will display the SUM of yellow and the RED cell.


The cell colors green, yellow and the blue in Column F were only added for visual assistance. They are not apart of the real spreadsheet.

The list varies and will not have set amounts. For example, one associate may have more TIPPED values. So there cannot be cell cell references in the code. There can also be 100+ associates.

[aimatarv]

Hi Sam,
thank you for your reply. As I walked away from the post after making it I figured someone would have made cell references. Unfortunately The list varies and will not have set amounts. For example, one associate may have more TIPPED values. So there cannot be cell cell references in the code. There can also be 100+ associates.

The way I was trying to write the code was find the number of occurrences of the Word "TIPPED" between the words "Time Code" and "Totals". I would find the sum of the range of those those "TIPPED" cells except 1 cell to the right and do the sum below those cells.

[SamT]

There are 42 way to do this. I tried to think of one whose logic was simple enough for a complete beginner to understand, so that you can change it if you aver need to rearrange the table.

I set it up so that if you DoubleClick a cell in column C inside the table, it will compute all the totals. If you DoubleClick a Cell in Column F, inside the table, it will erase all the Totals.

Place all this code in the Worksheet Code Page. RightClick on the Sheet Tab and select "View Code" to open that Code Page.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range(Range("F10"), Cells(Rows.Count, "F").End(xlUp))) _
Is Nothing Then
Aimatarv_ClearSubtotals 'Edit to match name of Sub
Cancel = True
End If

If Not Intersect(Target, Range(Range("C10"), Cells(Rows.Count, "C").End(xlUp))) _
Is Nothing Then
VBAX_SamT_Aimatarv_SubTotaling 'Edit to match name of Sub
Cancel = True
End If
End Sub


Sub VBAX_SamT_Aimatarv_SubTotaling()
'For help see: http://www.vbaexpress.com/forum/showthread.php?55189
Dim Rw As Long
Dim RateTotal As Double

For Rw = 10 To Cells(10, "B").End(xlDown).Row
If Cells(Rw, "A") = "" Then
If Cells(Rw, "C") <> 0 Then
RateTotal = RateTotal + Cells(Rw, "C")
Else
Cells(Rw, "C") = RateTotal
Cells(Rw, "F") = RateTotal + Cells(Rw, "E")
End If
Else
RateTotal = 0
End If
Next Rw

End Sub


Sub Aimatarv_ClearSubtotals()
'For help see: http://www.vbaexpress.com/forum/showthread.php?55189
Dim Rw As Long
Dim RateTotal As Double

For Rw = 11 To Cells(10, "B").End(xlDown).Row
If Cells(Rw, "A") <> "" Then
Cells(Rw - 1, "C").ClearContents
Cells(Rw - 1, "F").ClearContents
End If
Next Rw

Cells(Rw, "C").ClearContents
Cells(Rw, "F").ClearContents
End Sub

[aimatarv]

Wow, worked perfectly SamT! I really do appreciate the help.

The next step is to find and place cells of names with corressponding data to this spreadsheet. I'll research that on my own and I'll come right back if I can't figure it out. HAHH

Thanks!