Sum Multiple Columns Based on Multiple Criteria

aread

New Member
Joined
Dec 27, 2019
Messages
37
Office Version
365
Platform
Windows
I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does not matter. When summing columns G & I, the cell value is only the number even though it also shows the unit of measure when copied and pasted. I have removed our multiple vendors' names from column D due to confidentiality. I am hoping that someone will be able to assist because I keep hitting a brick wall. Thank you in advance.


MaterialPlantVendorVendor NameFiscal year/periodFiscal yearPO quantityOrder valueActual GR quantityGR valueInvoiced quantityInvoiced amountVar Inv Val vs PO valueUnit Price (Inv)
3000000122301005462JAN2017510,609 LB$ 146,527.87466,281 LB$ 133,598.54466,281 LB$ 134,712.33$ -11,815.54$ 0.29 / LB
3000000122301005462JUL201770,408 LB$ 21,524.7470,408 LB$ 21,524.7470,408 LB$ 21,798.97$ 274.23$ 0.31 / LB
3000000122301005462OCT2017192,092 LB$ 60,966.21141,904 LB$ 45,037.62141,904 LB$ 41,016.16$ -19,950.05$ 0.29 / LB
3000000122301005462DEC201771,966 LB$ 21,829.5771,966 LB$ 22,840.6971,966 LB$ 20,457.16$ -1,372.41$ 0.28 / LB
3000000122301005462FEB2018652,437 LB$ 208,916.42651,379 LB$ 205,397.84651,379 LB$ 216,981.38$ 8,064.96$ 0.33 / LB
3000000122301005462NOV2018132,000 LB$ 43,003.6269,339 LB$ 22,589.6769,339 LB$ 23,734.77$ -19,268.85$ 0.34 / LB
3000000122301005462JAN2019850,000 LB$ 266,149.75561,657 LB$ 180,367.64561,657 LB$ 169,588.80$ -96,560.95$ 0.30 / LB
3000000322301005462JAN20172,226,648 LB$ 581,034.262,226,648 LB$ 581,034.262,226,648 LB$ 591,520.58$ 10,486.32$ 0.27 / LB
3000000322301005462AUG20171,354,793 LB$ 387,965.431,302,618 LB$ 373,024.211,302,618 LB$ 355,510.42$ -32,455.01$ 0.27 / LB
3000000322301005462NOV2017504,907 LB$ 150,477.46504,907 LB$ 150,477.46504,907 LB$ 132,783.95$ -17,693.51$ 0.26 / LB
3000000322301005462FEB20181,994,822 LB$ 578,724.531,994,822 LB$ 558,457.981,994,822 LB$ 586,726.22$ 8,001.69$ 0.29 / LB
3000000322301005462OCT20181,138,598 LB$ 363,406.321,097,019 LB$ 350,135.541,097,019 LB$ 343,453.86$ -19,952.46$ 0.31 / LB
3000000322301005462JAN20191,973,753 LB$ 583,905.211,973,753 LB$ 583,905.211,973,753 LB$ 561,916.93$ -21,988.28$ 0.28 / LB
3000000322301005462APR2019388,151 LB$ 103,830.39388,151 LB$ 103,830.39388,151 LB$ 104,320.95$ 490.56$ 0.27 / LB
3000000322301005462NOV2019666,426 LB$ 144,947.66583,974 LB$ 127,014.34583,974 LB$ 127,014.24$ -17,933.42$ 0.22 / LB
3000000322301005462DEC20192,828,000 LB$ 615,090.00505,784 LB$ 110,008.03505,784 LB$ 104,473.99$ -510,616.01$ 0.21 / LB
3000000522301000562DEC2017148,490 LB$ 39,958.6684,000 LB$ 22,604.4084,000 LB$ 22,874.00$ -17,084.66$ 0.27 / LB
3000000522301005074NOV201842,000 LB$ 11,302.2042,000 LB$ 11,302.2042,000 LB$ 11,809.00$ 506.80$ 0.28 / LB
3000000522301005074APR2019106,490 LB$ 29,465.7884,000 LB$ 23,242.8084,000 LB$ 23,618.00$ -5,847.78$ 0.28 / LB
3000000522301005074DEC2019148,490 LB$ 42,572.080 LB$ 0.000 LB$ 0.00$ -42,572.08X
3000000622301004428AUG201730,240 LB$ 14,515.2015,000 LB$ 7,200.0015,000 LB$ 7,200.00$ -7,315.20$ 0.48 / LB
3000000622301004428NOV201751,260 LB$ 24,604.8045,000 LB$ 21,600.0045,000 LB$ 22,600.00$ -2,004.80$ 0.50 / LB
3000000622301004428NOV201860,000 LB$ 30,600.0060,000 LB$ 30,600.0060,000 LB$ 31,552.50$ 952.50$ 0.53 / LB
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,170
Office Version
2019
Platform
Windows
I would approach this task by placing your variables in the columns below the bottom of the report. Then employ the Sumifs function. If you are unfamiliar with this function, then look at this link for a tutorial and syntax.

 

aread

New Member
Joined
Dec 27, 2019
Messages
37
Office Version
365
Platform
Windows
The table provided is only a small subset of the data. There are a large number of materials at multiple plants and from multiple vendors, which is why I am looking at VBA. Do you still think the formula is the best option?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
922
Office Version
365, 2010
Also, if you sum G through M, aren't in doubling up on column I in column K?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,170
Office Version
2019
Platform
Windows
Actually, after looking it over a second time, I would create a pivot table to this.

Book1
ABCDEFGHIJK
3MaterialPlantVendorFiscal yearSum of PO quantitySum of Order valueSum of Actual GR quantitySum of GR valueSum of Invoiced quantitySum of Invoiced amountSum of Var Inv Val vs PO value
430000001
52230
61005462
72017845075250848.39750559223001.59750559217984.62-32863.77
82018784437251920.04720718227987.51720718240716.15-11203.89
92019850000266149.75561657180367.64561657169588.8-96560.95
1030000003
112230
121005462
13201740863481119477.1540341731104535.9340341731079814.95-39662.2
1420183133420942130.853091841908593.523091841930180.08-11950.77
15201958563301447773.263451662924757.973451662897726.11-550047.15
1630000005
172230
181000562
19201714849039958.668400022604.48400022874-17084.66
201005074
2120184200011302.24200011302.24200011809506.8
22201925498072037.868400023242.88400023618-48419.86
2330000006
242230
251004428
262017815003912060000288006000029800-9320
272018600003060060000306006000031552.5952.5
Sheet3
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,200
Office Version
2007
Platform
Windows
There are a large number of materials
How many records does your base have?
Could it be a macro that generates the summary on another sheet?
 

aread

New Member
Joined
Dec 27, 2019
Messages
37
Office Version
365
Platform
Windows
In the current data set, there are 3,639 rows of data, but that could increase based on the report criteria. A pivot table is not ideal because I need to be able to use a vlookup to pull the sums into another table, which is why I wanted to consolidate with vba.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,200
Office Version
2007
Platform
Windows
Try the following:
The result will be in the "summary" sheet.
Change in the macro "Sheet1" and "Summary" for the names of your sheets.

VBA Code:
Sub Sum_Multiple_Columns()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, j As Long, a As Variant, b As Variant
  Dim cad1 As String, cad2 As String, cad3 As String
  Dim val1 As Variant, val2 As Variant, vSum As Double
  Dim dic As Object, ky As Variant
  Application.ScreenUpdating = False
  
  Set sh1 = Sheets("Sheet1")    'source data
  Set sh2 = Sheets("Summary")  'destination
  sh2.Rows("2:" & Rows.Count).ClearContents
  
  Set dic = CreateObject("Scripting.Dictionary")
  dic.CompareMode = vbTextCompare
  a = sh1.Range("A2:M" & sh1.Range("A" & Rows.Count).End(xlUp).Row).Value2
  
  For i = 1 To UBound(a, 1)
    cad1 = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3) & "|" & a(i, 6)
    cad2 = Replace(Replace(a(i, 7) & "|" & a(i, 8) & "|" & a(i, 9) & "|" & _
           a(i, 10) & "|" & a(i, 11) & "|" & a(i, 12) & "|" & a(i, 13), " LB", ""), ",", "")
        
    If Not dic.exists(cad1) Then
      dic(cad1) = cad2
    Else
      val1 = Split(dic(cad1), "|")
      val2 = Split(cad2, "|")
      cad3 = ""
      For j = 0 To UBound(val1)
        vSum = Val(val1(j)) + Val(val2(j))
        cad3 = cad3 & "|" & vSum
      Next
      dic(cad1) = Mid(cad3, 2)
    End If
  Next
  
  sh2.Range("A2").Resize(dic.Count).Value = Application.Transpose(dic.keys)
  sh2.Range("A2").Resize(dic.Count).TextToColumns sh2.Range("A2"), _
    xlDelimited, xlTextQualifierDoubleQuote, , , , , , True, "|"
  sh2.Range("E2").Resize(dic.Count).Value = Application.Transpose(dic.items)
  sh2.Range("E2").Resize(dic.Count).TextToColumns sh2.Range("E2"), _
    xlDelimited, xlTextQualifierDoubleQuote, , , , , , True, "|"

End Sub
 

aread

New Member
Joined
Dec 27, 2019
Messages
37
Office Version
365
Platform
Windows
That worked great! Thank you so much! Is there an easy way to pull the number formatting from the original cells being compiled? There is custom formatting that includes "TON" and "LB"

1581523143372.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,200
Office Version
2007
Platform
Windows
Is there an easy way to pull the number formatting from the original cells being compiled?
Do you want to remove the format? If so, select the cells and change the format to "General".
If not, you could explain a little more what you need.
 

Forum statistics

Threads
1,089,519
Messages
5,408,758
Members
403,225
Latest member
funny_bunny

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top