What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total customer
payments and total company payments for a given customer. When the total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not have to
pay until the total customer payments reach another amount (can also be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals where
there is the string of 0's? That would be the "gap" period for this customer.
It will not be as simple as sumif. I only want to calculate where there is a
consistant string of zeros.

Any suggestions?

A bit more clarification with examples of the correct answer sought.

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in message
...
What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total customer
payments and total company payments for a given customer. When the total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not have
to
pay until the total customer payments reach another amount (can also be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where there is
a
consistant string of zeros.

Any suggestions?

Yeah I thought that was a bit fuzzy.

I need to find a way to sum the total payments that occur within a gap
period. The answer I would be looking for in the example I provided is
1460.6. This would be the period where the consecutive zeros occur.

The problem is that consecutive zeros can also occur during the initial
payments being made due to a deductible. The deductibles can also vary. Once
the gap period is determined and calculated I then need to have a way to
distiguish the date when the gap starts and ends. Each entry has a date
associate with when the payment is occuring.

Here is another example where a deductible is occuring:

53.35 0
53.93 0
157.49 0
13.81 0 <--- The preceding zeros would be the deductible period
35 21.39
58.62 0
15 15.27
58.62 0
35 18.93
5 1.14
5 11.71
35 122.49
5 9.1
35 21.39
5 1.14
35 18.93
5 11.71
5 6.11
15 15.27
5 9.1
5 6.11
35 21.39
35 122.49
5 9.1
5 1.14
5 6.11
35 130.96
15 15.27
5 6.11
35 21.39
5 11.71
5 9.1
35 18.93
5 6.11
5 9.1
5 1.14
35 18.93
5 11.71
5 11.71
5 9.1
5 1.14
5 6.11
5 6.11
35 18.93
5 11.71
42.27 123.69
30.27 0
14.1 0
6.14 0
116.31 0
16.71 0
11.11 0
6.14 0
28.45 0
16.71 0
165.96 0
14.1 0
16.71 0
56.39 0
36.01 0
16.71 0 <- These preceding zeros all the way up to (not including) the
123.69 would be the gap period I am trying to determine.

The answer I would be looking for here would be 551.82.

Is this making sense yet? There has got to be a way to determine the gap
period. I have thousands of records just like this.

Any suggestions?



"Don Guillett" wrote:

A bit more clarification with examples of the correct answer sought.

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in message
...
What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total customer
payments and total company payments for a given customer. When the total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not have
to
pay until the total customer payments reach another amount (can also be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where there is
a
consistant string of zeros.

Any suggestions?

try this

Sub FindGapRow()
mc = 3
fa = 1
la = Cells(Rows.Count, mc).End(xlUp).row
Do Until fa = la
For Each c In Range(Cells(fa, mc), Cells(la, mc))
If c = 0 And c.Offset(1) = 0 Then
fadd = c.row
Exit For
End If
Next c
For Each c In Range(Cells(fadd, mc), Cells(la, mc))
If c < 0 And c.Offset(-1) = 0 Then
ladd = c.row - 1
c.Offset(-1, 1) = c.Offset(-1, -2)
Exit For
End If
Next c
fa = ladd + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"IntricateFool" wrote in message
...
Yeah I thought that was a bit fuzzy.

I need to find a way to sum the total payments that occur within a gap
period. The answer I would be looking for in the example I provided is
1460.6. This would be the period where the consecutive zeros occur.

The problem is that consecutive zeros can also occur during the initial
payments being made due to a deductible. The deductibles can also vary.
Once
the gap period is determined and calculated I then need to have a way to
distiguish the date when the gap starts and ends. Each entry has a date
associate with when the payment is occuring.

Here is another example where a deductible is occuring:

53.35 0
53.93 0
157.49 0
13.81 0 <--- The preceding zeros would be the deductible period
35 21.39
58.62 0
15 15.27
58.62 0
35 18.93
5 1.14
5 11.71
35 122.49
5 9.1
35 21.39
5 1.14
35 18.93
5 11.71
5 6.11
15 15.27
5 9.1
5 6.11
35 21.39
35 122.49
5 9.1
5 1.14
5 6.11
35 130.96
15 15.27
5 6.11
35 21.39
5 11.71
5 9.1
35 18.93
5 6.11
5 9.1
5 1.14
35 18.93
5 11.71
5 11.71
5 9.1
5 1.14
5 6.11
5 6.11
35 18.93
5 11.71
42.27 123.69
30.27 0
14.1 0
6.14 0
116.31 0
16.71 0
11.11 0
6.14 0
28.45 0
16.71 0
165.96 0
14.1 0
16.71 0
56.39 0
36.01 0
16.71 0 <- These preceding zeros all the way up to (not including) the
123.69 would be the gap period I am trying to determine.

The answer I would be looking for here would be 551.82.

Is this making sense yet? There has got to be a way to determine the gap
period. I have thousands of records just like this.

Any suggestions?



"Don Guillett" wrote:

A bit more clarification with examples of the correct answer sought.

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in
message
...
What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total
customer
payments and total company payments for a given customer. When the
total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not
have
to
pay until the total customer payments reach another amount (can also be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals
where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where there
is
a
consistant string of zeros.

Any suggestions?

This gives both dates and the totals

Sub FindGapRow()
mc = 3
fa = 1
la = Cells(Rows.Count, mc).End(xlUp).row
Do Until fa = la
For Each c In Range(Cells(fa, mc), Cells(la, mc))
If c = 0 And c.Offset(1) = 0 Then
fadd = c.row
c.Offset(, 1) = c.Offset(, -2)
Exit For
End If
Next c
For Each c In Range(Cells(fadd, mc), Cells(la, mc))
If c < 0 And c.Offset(-1) = 0 Then
ladd = c.row - 1
c.Offset(-1, 1) = c.Offset(-1, -2)
c.Offset(-1, 2) = _
Application.Sum(Range(Cells(fadd, mc - 1), Cells(ladd, mc - 1)))
Exit For
End If
Next c
fa = ladd + 1
Loop
End Sub

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in message
...
Yeah I thought that was a bit fuzzy.

I need to find a way to sum the total payments that occur within a gap
period. The answer I would be looking for in the example I provided is
1460.6. This would be the period where the consecutive zeros occur.

The problem is that consecutive zeros can also occur during the initial
payments being made due to a deductible. The deductibles can also vary.
Once
the gap period is determined and calculated I then need to have a way to
distiguish the date when the gap starts and ends. Each entry has a date
associate with when the payment is occuring.

Here is another example where a deductible is occuring:

53.35 0
53.93 0
157.49 0
13.81 0 <--- The preceding zeros would be the deductible period
35 21.39
58.62 0
15 15.27
58.62 0
35 18.93
5 1.14
5 11.71
35 122.49
5 9.1
35 21.39
5 1.14
35 18.93
5 11.71
5 6.11
15 15.27
5 9.1
5 6.11
35 21.39
35 122.49
5 9.1
5 1.14
5 6.11
35 130.96
15 15.27
5 6.11
35 21.39
5 11.71
5 9.1
35 18.93
5 6.11
5 9.1
5 1.14
35 18.93
5 11.71
5 11.71
5 9.1
5 1.14
5 6.11
5 6.11
35 18.93
5 11.71
42.27 123.69
30.27 0
14.1 0
6.14 0
116.31 0
16.71 0
11.11 0
6.14 0
28.45 0
16.71 0
165.96 0
14.1 0
16.71 0
56.39 0
36.01 0
16.71 0 <- These preceding zeros all the way up to (not including) the
123.69 would be the gap period I am trying to determine.

The answer I would be looking for here would be 551.82.

Is this making sense yet? There has got to be a way to determine the gap
period. I have thousands of records just like this.

Any suggestions?



"Don Guillett" wrote:

A bit more clarification with examples of the correct answer sought.

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in
message
...
What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total
customer
payments and total company payments for a given customer. When the
total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not
have
to
pay until the total customer payments reach another amount (can also be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals
where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where there
is
a
consistant string of zeros.

Any suggestions?

You are awesome! Thanks

I want to gain the best understanding of how this is actually working. Can
you walk me through what each part of this code is doing (I understand the
for, next and offset, but what this is looking for and checkin)? Also, how
can I apply this to certain columns of my worksheet? The two columns that I
provided for examples are only one piece of each worksheet.

I want to learn how to do this type of VBA programming on my own. Do you
recommend a starting spot?

Thanks!

"Don Guillett" wrote:

This gives both dates and the totals

Sub FindGapRow()
mc = 3
fa = 1
la = Cells(Rows.Count, mc).End(xlUp).row
Do Until fa = la
For Each c In Range(Cells(fa, mc), Cells(la, mc))
If c = 0 And c.Offset(1) = 0 Then
fadd = c.row
c.Offset(, 1) = c.Offset(, -2)
Exit For
End If
Next c
For Each c In Range(Cells(fadd, mc), Cells(la, mc))
If c < 0 And c.Offset(-1) = 0 Then
ladd = c.row - 1
c.Offset(-1, 1) = c.Offset(-1, -2)
c.Offset(-1, 2) = _
Application.Sum(Range(Cells(fadd, mc - 1), Cells(ladd, mc - 1)))
Exit For
End If
Next c
fa = ladd + 1
Loop
End Sub

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in message
...
Yeah I thought that was a bit fuzzy.

I need to find a way to sum the total payments that occur within a gap
period. The answer I would be looking for in the example I provided is
1460.6. This would be the period where the consecutive zeros occur.

The problem is that consecutive zeros can also occur during the initial
payments being made due to a deductible. The deductibles can also vary.
Once
the gap period is determined and calculated I then need to have a way to
distiguish the date when the gap starts and ends. Each entry has a date
associate with when the payment is occuring.

Here is another example where a deductible is occuring:

53.35 0
53.93 0
157.49 0
13.81 0 <--- The preceding zeros would be the deductible period
35 21.39
58.62 0
15 15.27
58.62 0
35 18.93
5 1.14
5 11.71
35 122.49
5 9.1
35 21.39
5 1.14
35 18.93
5 11.71
5 6.11
15 15.27
5 9.1
5 6.11
35 21.39
35 122.49
5 9.1
5 1.14
5 6.11
35 130.96
15 15.27
5 6.11
35 21.39
5 11.71
5 9.1
35 18.93
5 6.11
5 9.1
5 1.14
35 18.93
5 11.71
5 11.71
5 9.1
5 1.14
5 6.11
5 6.11
35 18.93
5 11.71
42.27 123.69
30.27 0
14.1 0
6.14 0
116.31 0
16.71 0
11.11 0
6.14 0
28.45 0
16.71 0
165.96 0
14.1 0
16.71 0
56.39 0
36.01 0
16.71 0 <- These preceding zeros all the way up to (not including) the
123.69 would be the gap period I am trying to determine.

The answer I would be looking for here would be 551.82.

Is this making sense yet? There has got to be a way to determine the gap
period. I have thousands of records just like this.

Any suggestions?



"Don Guillett" wrote:

A bit more clarification with examples of the correct answer sought.

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in
message
...
What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total
customer
payments and total company payments for a given customer. When the
total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not
have
to
pay until the total customer payments reach another amount (can also be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals
where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where there
is
a
consistant string of zeros.

Any suggestions?

You can incorporate the column variables into a for/each loop as I did here.
Contact me privately for projects.
--
Don Guillett
SalesAid Software

"IntricateFool" wrote in message
...
You are awesome! Thanks

I want to gain the best understanding of how this is actually working. Can
you walk me through what each part of this code is doing (I understand the
for, next and offset, but what this is looking for and checkin)? Also, how
can I apply this to certain columns of my worksheet? The two columns that
I
provided for examples are only one piece of each worksheet.

I want to learn how to do this type of VBA programming on my own. Do you
recommend a starting spot?

Thanks!

"Don Guillett" wrote:

This gives both dates and the totals

Sub FindGapRow()
mc = 3
fa = 1
la = Cells(Rows.Count, mc).End(xlUp).row
Do Until fa = la
For Each c In Range(Cells(fa, mc), Cells(la, mc))
If c = 0 And c.Offset(1) = 0 Then
fadd = c.row
c.Offset(, 1) = c.Offset(, -2)
Exit For
End If
Next c
For Each c In Range(Cells(fadd, mc), Cells(la, mc))
If c < 0 And c.Offset(-1) = 0 Then
ladd = c.row - 1
c.Offset(-1, 1) = c.Offset(-1, -2)
c.Offset(-1, 2) = _
Application.Sum(Range(Cells(fadd, mc - 1), Cells(ladd, mc - 1)))
Exit For
End If
Next c
fa = ladd + 1
Loop
End Sub

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in
message
...
Yeah I thought that was a bit fuzzy.

I need to find a way to sum the total payments that occur within a gap
period. The answer I would be looking for in the example I provided is
1460.6. This would be the period where the consecutive zeros occur.

The problem is that consecutive zeros can also occur during the initial
payments being made due to a deductible. The deductibles can also vary.
Once
the gap period is determined and calculated I then need to have a way
to
distiguish the date when the gap starts and ends. Each entry has a date
associate with when the payment is occuring.

Here is another example where a deductible is occuring:

53.35 0
53.93 0
157.49 0
13.81 0 <--- The preceding zeros would be the deductible period
35 21.39
58.62 0
15 15.27
58.62 0
35 18.93
5 1.14
5 11.71
35 122.49
5 9.1
35 21.39
5 1.14
35 18.93
5 11.71
5 6.11
15 15.27
5 9.1
5 6.11
35 21.39
35 122.49
5 9.1
5 1.14
5 6.11
35 130.96
15 15.27
5 6.11
35 21.39
5 11.71
5 9.1
35 18.93
5 6.11
5 9.1
5 1.14
35 18.93
5 11.71
5 11.71
5 9.1
5 1.14
5 6.11
5 6.11
35 18.93
5 11.71
42.27 123.69
30.27 0
14.1 0
6.14 0
116.31 0
16.71 0
11.11 0
6.14 0
28.45 0
16.71 0
165.96 0
14.1 0
16.71 0
56.39 0
36.01 0
16.71 0 <- These preceding zeros all the way up to (not including) the
123.69 would be the gap period I am trying to determine.

The answer I would be looking for here would be 551.82.

Is this making sense yet? There has got to be a way to determine the
gap
period. I have thousands of records just like this.

Any suggestions?



"Don Guillett" wrote:

A bit more clarification with examples of the correct answer sought.

--
Don Guillett
SalesAid Software

"IntricateFool" wrote in
message
...
What would be the best way to go about summing values based on
certain
criteria. I have many different spreadsheets that contain total
customer
payments and total company payments for a given customer. When the
total
customer payment for a given customer reaches a certain number (can
be
different on each worksheet) that customer hits a "gap" and does not
have
to
pay until the total customer payments reach another amount (can also
be
different on each worksheet).

For example the two columns being referred to would look like:

5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2

How would I go about accurately calculating the first columns totals
where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where
there
is
a
consistant string of zeros.

Any suggestions?