Sum values only if consecutive numbers

rachelih

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Row 39 which varies by month (across columns) has some months with values and some without:

1605105692028.png


GOAL: I would like to have a formula in cell G41 that sums row 39 (highlighted) ONLY IF there are 6 consecutive months where values in row 39 are greater than 0. In other words, G41 should total to $544,560.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Try this:

=IF(MAX(FREQUENCY(IF(G39:R39>0, COLUMN(G39:R39)), IF(G39:R39<=0, COLUMN(G39:R39))))>=6,SUM(G39:R39),"")
 

rachelih

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Thanks for the try. This provides a total of 579,914, which means it's still summing the entire row, not just that are at least six consecutive months, namely March through September. So October and January should not be included in the sum since they are not part of the >=6 consecutive month bucket.
 

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Thanks for the try. This provides a total of 579,914, which means it's still summing the entire row, not just that are at least six consecutive months, namely March through September. So October and January should not be included in the sum since they are not part of the >=6 consecutive month bucket.
Sorry, I thought you meant sum the whole row if there are 6 or more consecutive >0. Can you add a helper row, or does it need to all be in one formula?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If your happy with a UDF, how about
VBA Code:
Function rachelih(Rng As Range) As Double
   Dim Cl As Range
   Dim Tot As Double
   Dim i As Long
   
   For Each Cl In Rng
      If Cl.Value > 0 Then
         Tot = Tot + Cl.Value
         i = i + 1
      ElseIf i < 6 Then
         Tot = 0
         i = 0
      Else
         Exit For
      End If
   Next Cl
   rachelih = Tot
End Function
+Fluff v2.xlsm
FGHIJKLMNOPQR
1
2589900294550425129341552242689381414169267853359
3544560
4
Master
Cell Formulas
RangeFormula
G3G3=rachelih(G2:R2)
 

rachelih

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Sorry, I thought you meant sum the whole row if there are 6 or more consecutive >0. Can you add a helper row, or does it need to all be in one formula?
I can do a helper row. Was actually going to suggest maybe there's some way to make a formula using a helper row
 

rachelih

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If your happy with a UDF, how about
VBA Code:
Function rachelih(Rng As Range) As Double
   Dim Cl As Range
   Dim Tot As Double
   Dim i As Long
 
   For Each Cl In Rng
      If Cl.Value > 0 Then
         Tot = Tot + Cl.Value
         i = i + 1
      ElseIf i < 6 Then
         Tot = 0
         i = 0
      Else
         Exit For
      End If
   Next Cl
   rachelih = Tot
End Function
Super cool! And works ;), thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

rachelih

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
If your happy with a UDF, how about
VBA Code:
Function rachelih(Rng As Range) As Double
   Dim Cl As Range
   Dim Tot As Double
   Dim i As Long
 
   For Each Cl In Rng
      If Cl.Value > 0 Then
         Tot = Tot + Cl.Value
         i = i + 1
      ElseIf i < 6 Then
         Tot = 0
         i = 0
      Else
         Exit For
      End If
   Next Cl
   rachelih = Tot
End Function
I'm back. Found some issues with the code, whereby it wasn't properly working. None of the below should have totaled to expected revenue > 0 because there aren't 6 consecutive months > 0. I'd be happy to do a helper row instead of a UDF if that would be easier. Thx in advance!

1605561909031.png


1605561952748.png
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Function rachelih(Rng As Range) As Double
   Dim Cl As Range
   Dim Tot As Double
   Dim i As Long
   
   For Each Cl In Rng
      If Cl.Value > 0 Then
         Tot = Tot + Cl.Value
         i = i + 1
      ElseIf i < 6 Then
         Tot = 0
         i = 0
      Else
         Exit For
      End If
   Next Cl
   rachelih = IIf(i < 6, 0, Tot)
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top