Sum numbers using two row and sum the numbers greater then 0 using row 1 as the default

frankday

Board Regular
Joined
Apr 13, 2012
Messages
94
I have row 1 (2019) totals Row 2 (2018) totals. Row 2 has all 12 months worth of data. Row 1 has 9 months worth of data. I need the sum of all 9 months in row 1 and the last 3 of row 2. That easy enough. Now when I collect the totals for the 10th month of row 1 I then need the total for months 1 - 10 of row 1 and month 11-12 of row 2. How can I do with without creating 12 different if statements.

 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
You can try this. Formula R6 sums the current row's monthly values and uses the prior year's values for empty months.


[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">1</td><td style="text-align: right;background-color: #FFF2CC;;">2</td><td style="text-align: right;background-color: #FFF2CC;;">3</td><td style="text-align: right;background-color: #FFF2CC;;">4</td><td style="text-align: right;background-color: #FFF2CC;;">5</td><td style="text-align: right;background-color: #FFF2CC;;">6</td><td style="text-align: right;background-color: #FFF2CC;;">7</td><td style="text-align: right;background-color: #FFF2CC;;">8</td><td style="text-align: right;background-color: #FFF2CC;;">9</td><td style="text-align: right;background-color: #FFF2CC;;">10</td><td style="text-align: right;background-color: #FFF2CC;;">11</td><td style="text-align: right;background-color: #FFF2CC;;">12</td><td style="background-color: #FCE4D6;;">Sum_row</td><td style="background-color: #FCE4D6;;">Sum_Projected</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">2019</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">900</td><td style="text-align: right;background-color: #E2EFDA;;">930</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">2018</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet43</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q6</th><td style="text-align:left">=SUM(<font color="Blue">E6:P6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R6</th><td style="text-align:left">=SUM(<font color="Blue">IF(<font color="Red">E6:P6,"",OFFSET(<font color="Green">E6:P6,1,</font>)</font>)</font>)+SUM(<font color="Blue">E6:P6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q7</th><td style="text-align:left">=SUM(<font color="Blue">E7:P7</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 
Last edited:

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top