To calculate based on names

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
254
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,788
Office Version
2007
Platform
Windows
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

Active Member
Joined
Mar 26, 2019
Messages
254
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,788
Office Version
2007
Platform
Windows
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

Active Member
Joined
Mar 26, 2019
Messages
254
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,788
Office Version
2007
Platform
Windows
According to your last example, the macro could be like this:

Code:
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.

Code:
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

Active Member
Joined
Mar 26, 2019
Messages
254
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,788
Office Version
2007
Platform
Windows
I did not understand what you mean.
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
254
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,788
Office Version
2007
Platform
Windows
:confused: I still do not understand. What is the request?
 

Forum statistics

Threads
1,081,849
Messages
5,361,677
Members
400,644
Latest member
ndroger1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top