To calculate based on names

[GirishDhruva]

Hi Everyone,

Here i am trying to calculate the value based on the headers which are in the "sum" sheet and their names will only be A,B,C......, and my inputs are in "main" sheet but in my input those columns will be renamed as Sum of A,Sum of B,Sum of C..........., and my preferred output should be in "main"sheet only .

Here i have attached my sample workbook.
https://www.dropbox.com/s/fitbvqde265b9e0/mrexcel_sum.xlsm?dl=0

Regards,
Dhruva.

 

[DanteAmor]

According to your examples, use the following formula array

Main
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td colspan="16" style="text-align:center; ">Input</td><td > </td><td colspan="5" style="text-align:center; ">Output</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of A</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of B</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of C</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of D</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of E</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of F</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of G</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of H</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of I</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of J</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of K</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of L</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of M</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of N</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of O</td><td style="background-color:#b3c6e7; font-weight:bold; ">Sum of P</td><td > </td><td style="background-color:#b3c6e7; ">Apple</td><td style="background-color:#b3c6e7; ">Orange</td><td style="background-color:#b3c6e7; ">Banana</td><td style="background-color:#b3c6e7; ">Grapes</td><td style="background-color:#b3c6e7; ">Seeds</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td style="text-align:right; ">8</td><td style="text-align:right; ">9</td><td style="text-align:right; ">10</td><td style="text-align:right; ">11</td><td style="text-align:right; ">12</td><td style="text-align:right; ">13</td><td style="text-align:right; ">14</td><td style="text-align:right; ">15</td><td style="text-align:right; ">16</td><td > </td><td style="text-align:right; ">35</td><td style="text-align:right; ">33</td><td style="text-align:right; ">16</td><td style="text-align:right; ">30</td><td style="text-align:right; ">22</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2</td><td style="text-align:right; ">4</td><td style="text-align:right; ">6</td><td style="text-align:right; ">8</td><td style="text-align:right; ">10</td><td style="text-align:right; ">12</td><td style="text-align:right; ">14</td><td style="text-align:right; ">16</td><td style="text-align:right; ">18</td><td style="text-align:right; ">20</td><td style="text-align:right; ">22</td><td style="text-align:right; ">24</td><td style="text-align:right; ">26</td><td style="text-align:right; ">28</td><td style="text-align:right; ">30</td><td style="text-align:right; ">32</td><td > </td><td style="text-align:right; ">70</td><td style="text-align:right; ">66</td><td style="text-align:right; ">32</td><td style="text-align:right; ">60</td><td style="text-align:right; ">44</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">3</td><td style="text-align:right; ">6</td><td style="text-align:right; ">9</td><td style="text-align:right; ">12</td><td style="text-align:right; ">15</td><td style="text-align:right; ">18</td><td style="text-align:right; ">21</td><td style="text-align:right; ">24</td><td style="text-align:right; ">27</td><td style="text-align:right; ">30</td><td style="text-align:right; ">33</td><td style="text-align:right; ">36</td><td style="text-align:right; ">39</td><td style="text-align:right; ">42</td><td style="text-align:right; ">45</td><td style="text-align:right; ">48</td><td > </td><td style="text-align:right; ">105</td><td style="text-align:right; ">99</td><td style="text-align:right; ">48</td><td style="text-align:right; ">90</td><td style="text-align:right; ">66</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4</td><td style="text-align:right; ">8</td><td style="text-align:right; ">12</td><td style="text-align:right; ">16</td><td style="text-align:right; ">20</td><td style="text-align:right; ">24</td><td style="text-align:right; ">28</td><td style="text-align:right; ">32</td><td style="text-align:right; ">36</td><td style="text-align:right; ">40</td><td style="text-align:right; ">44</td><td style="text-align:right; ">48</td><td style="text-align:right; ">52</td><td style="text-align:right; ">56</td><td style="text-align:right; ">60</td><td style="text-align:right; ">64</td><td > </td><td style="text-align:right; ">140</td><td style="text-align:right; ">132</td><td style="text-align:right; ">64</td><td style="text-align:right; ">120</td><td style="text-align:right; ">88</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">5</td><td style="text-align:right; ">10</td><td style="text-align:right; ">15</td><td style="text-align:right; ">20</td><td style="text-align:right; ">25</td><td style="text-align:right; ">30</td><td style="text-align:right; ">35</td><td style="text-align:right; ">40</td><td style="text-align:right; ">45</td><td style="text-align:right; ">50</td><td style="text-align:right; ">55</td><td style="text-align:right; ">60</td><td style="text-align:right; ">65</td><td style="text-align:right; ">70</td><td style="text-align:right; ">75</td><td style="text-align:right; ">80</td><td > </td><td style="text-align:right; ">175</td><td style="text-align:right; ">165</td><td style="text-align:right; ">80</td><td style="text-align:right; ">150</td><td style="text-align:right; ">110</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">6</td><td style="text-align:right; ">12</td><td style="text-align:right; ">18</td><td style="text-align:right; ">24</td><td style="text-align:right; ">30</td><td style="text-align:right; ">36</td><td style="text-align:right; ">42</td><td style="text-align:right; ">48</td><td style="text-align:right; ">54</td><td style="text-align:right; ">60</td><td style="text-align:right; ">66</td><td style="text-align:right; ">72</td><td style="text-align:right; ">78</td><td style="text-align:right; ">84</td><td style="text-align:right; ">90</td><td style="text-align:right; ">96</td><td > </td><td style="text-align:right; ">210</td><td style="text-align:right; ">198</td><td style="text-align:right; ">96</td><td style="text-align:right; ">180</td><td style="text-align:right; ">132</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">7</td><td style="text-align:right; ">14</td><td style="text-align:right; ">21</td><td style="text-align:right; ">28</td><td style="text-align:right; ">35</td><td style="text-align:right; ">42</td><td style="text-align:right; ">49</td><td style="text-align:right; ">56</td><td style="text-align:right; ">63</td><td style="text-align:right; ">70</td><td style="text-align:right; ">77</td><td style="text-align:right; ">84</td><td style="text-align:right; ">91</td><td style="text-align:right; ">98</td><td style="text-align:right; ">105</td><td style="text-align:right; ">112</td><td > </td><td style="text-align:right; ">245</td><td style="text-align:right; ">231</td><td style="text-align:right; ">112</td><td style="text-align:right; ">210</td><td style="text-align:right; ">154</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">8</td><td style="text-align:right; ">16</td><td style="text-align:right; ">24</td><td style="text-align:right; ">32</td><td style="text-align:right; ">40</td><td style="text-align:right; ">48</td><td style="text-align:right; ">56</td><td style="text-align:right; ">64</td><td style="text-align:right; ">72</td><td style="text-align:right; ">80</td><td style="text-align:right; ">88</td><td style="text-align:right; ">96</td><td style="text-align:right; ">104</td><td style="text-align:right; ">112</td><td style="text-align:right; ">120</td><td style="text-align:right; ">128</td><td > </td><td style="text-align:right; ">280</td><td style="text-align:right; ">264</td><td style="text-align:right; ">128</td><td style="text-align:right; ">240</td><td style="text-align:right; ">176</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >R4</td><td >{=SUM(IF(ISNUMBER(MATCH(COLUMN($A4:$P4),MATCH(INDEX(sum!$A$2:$E$17,,MATCH(R$3,sum!$A$1:$E$1,0)),SUBSTITUTE(ADDRESS(1,COLUMN($A$4:$P$4),4),"1",""),0),0)),$A4:$P4))}</td></tr></table></td></tr></table>
sum
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /><col style="width:60.83px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#b3c6e7; ">Apple</td><td style="background-color:#b3c6e7; ">Orange</td><td style="background-color:#b3c6e7; ">Banana</td><td style="background-color:#b3c6e7; ">Grapes</td><td style="background-color:#b3c6e7; ">Seeds</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >B</td><td >A</td><td >D</td><td >E</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >C</td><td >I</td><td >L</td><td >K</td><td >O</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >F</td><td >J</td><td > </td><td >N</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >H</td><td >M</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >P</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

 

[GirishDhruva]

Thanks, but i request in vba coding.
and based on headers from "sum sheet" can we sum the values in main sheet.

headers can be two or more but based on those headers i need to sum the values which are in main sheet.

Regards,
Dhruva.

 

[DanteAmor]

Well, you didn't mention it should be in VBA.


Then the headings on the 2 sheets must be the same.


You could send a new example, but with more real information. That is, on the "sum" sheet it says below Apple says "B" but on the "Main" sheet it says "sum of B", these headings are not the same.

 

[GirishDhruva]

Well, you didn't mention it should be in VBA.


Then the headings on the 2 sheets must be the same.


You could send a new example, but with more real information. That is, on the "sum" sheet it says below Apple says "B" but on the "Main" sheet it says "sum of B", these headings are not the same.

Sorry For not mentioning about VBA.


only the "Sum of" will be added in front of header name.
Like,
if the header name is "VDA" in sum sheet, then the name in main sheet will be "Sum of VDA"

Here is my new information
https://www.dropbox.com/s/fitbvqde265b9e0/mrexcel_sum.xlsm?dl=0

Regards,
Dhruva.

 

[DanteAmor]

According to your last example, the macro could be like this:

Sub Macro1()
  Dim r As Range
  Set r = Range("R4:V" & Range("A" & Rows.Count).End(xlUp).Row)
  With Range("Q4")
    .FormulaArray = _
      "=SUM(IF(ISNUMBER(MATCH(COLUMN($A$3:$P$3),MATCH(""Sum of "" & " & _
      "INDEX(sum!$A$2:$E$17,,MATCH(Q$3,sum!$A$1:$E$1,0)),$A$3:$P$3,0),0)),$A4:$P4))"
    .Copy r
    r.Value = r.Value
    .Value = ""
  End With
End Sub

----------------------
But if you are going to have more columns then it can be the next macro, considering that the headings start in cell A3.

Sub calculate_based_on_names()
  Dim sh1 As Worksheet, sh2 As Worksheet, r1 As Range, r2 As Range
  Dim lr As Long, lc As Long, lc2 As Long
  Dim c As Range, f As Range, i As Long, col As Long, j As Long
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Main")
  Set sh2 = Sheets("sum")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.[COLOR=#0000ff]Range("A3")[/COLOR].End(xlToRight).Column
  Set r1 = sh1.[COLOR=#0000ff]Range("A3"[/COLOR], Cells(lr, lc))
  lc2 = sh1.Cells(3, lc + 2).End(xlToRight).Column
  Set r2 = sh1.Range(sh1.Cells(3, lc + 2), sh1.Cells(3, lc2))
  r2.Offset(1).Resize(lr).ClearContents
  For Each c In r2
    Set f = sh2.Rows(1).Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      col = f.Column
      For i = 2 To sh2.Cells(Rows.Count, col).End(xlUp).Row
        Set f = r1.Find("Sum of " & sh2.Cells(i, col), , xlValues, xlWhole)
        If Not f Is Nothing Then
          For j = c.Offset(1).Row To lr
            sh1.Cells(j, c.Column) = sh1.Cells(j, c.Column) + sh1.Cells(j, f.Column)
          Next
        End If
      Next
    End If
  Next
End Sub

 

[GirishDhruva]

Working Perfectly thank you @Dante Sir

can we add the same headers as in Sum sheet to main sheet before adding the values below them.

Regards,
Dhruva.

 

[DanteAmor]

I did not understand what you mean.

 

[GirishDhruva]

while calculating in main sheet it should have headers same as "sum sheet"so before calculating in "main sheet", headers should be copied from sum sheet to main sheet then it should calculate.

 

[DanteAmor]

I still do not understand. What is the request?