Sum Multiple Columns Based on Multiple Criteria

[aread]

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.

Material	Plant	Vendor	Vendor Name	Fiscal year/period	Fiscal year	PO quantity	Order value	Actual GR quantity	GR value	Invoiced quantity	Invoiced amount	Var Inv Val vs PO value	Unit Price (Inv)
30000001	2230	1005462		JAN	2017	510,609 LB	$ 146,527.87	466,281 LB	$ 133,598.54	466,281 LB	$ 134,712.33	$ -11,815.54	$ 0.29 / LB
30000001	2230	1005462		JUL	2017	70,408 LB	$ 21,524.74	70,408 LB	$ 21,524.74	70,408 LB	$ 21,798.97	$ 274.23	$ 0.31 / LB
30000001	2230	1005462		OCT	2017	192,092 LB	$ 60,966.21	141,904 LB	$ 45,037.62	141,904 LB	$ 41,016.16	$ -19,950.05	$ 0.29 / LB
30000001	2230	1005462		DEC	2017	71,966 LB	$ 21,829.57	71,966 LB	$ 22,840.69	71,966 LB	$ 20,457.16	$ -1,372.41	$ 0.28 / LB
30000001	2230	1005462		FEB	2018	652,437 LB	$ 208,916.42	651,379 LB	$ 205,397.84	651,379 LB	$ 216,981.38	$ 8,064.96	$ 0.33 / LB
30000001	2230	1005462		NOV	2018	132,000 LB	$ 43,003.62	69,339 LB	$ 22,589.67	69,339 LB	$ 23,734.77	$ -19,268.85	$ 0.34 / LB
30000001	2230	1005462		JAN	2019	850,000 LB	$ 266,149.75	561,657 LB	$ 180,367.64	561,657 LB	$ 169,588.80	$ -96,560.95	$ 0.30 / LB
30000003	2230	1005462		JAN	2017	2,226,648 LB	$ 581,034.26	2,226,648 LB	$ 581,034.26	2,226,648 LB	$ 591,520.58	$ 10,486.32	$ 0.27 / LB
30000003	2230	1005462		AUG	2017	1,354,793 LB	$ 387,965.43	1,302,618 LB	$ 373,024.21	1,302,618 LB	$ 355,510.42	$ -32,455.01	$ 0.27 / LB
30000003	2230	1005462		NOV	2017	504,907 LB	$ 150,477.46	504,907 LB	$ 150,477.46	504,907 LB	$ 132,783.95	$ -17,693.51	$ 0.26 / LB
30000003	2230	1005462		FEB	2018	1,994,822 LB	$ 578,724.53	1,994,822 LB	$ 558,457.98	1,994,822 LB	$ 586,726.22	$ 8,001.69	$ 0.29 / LB
30000003	2230	1005462		OCT	2018	1,138,598 LB	$ 363,406.32	1,097,019 LB	$ 350,135.54	1,097,019 LB	$ 343,453.86	$ -19,952.46	$ 0.31 / LB
30000003	2230	1005462		JAN	2019	1,973,753 LB	$ 583,905.21	1,973,753 LB	$ 583,905.21	1,973,753 LB	$ 561,916.93	$ -21,988.28	$ 0.28 / LB
30000003	2230	1005462		APR	2019	388,151 LB	$ 103,830.39	388,151 LB	$ 103,830.39	388,151 LB	$ 104,320.95	$ 490.56	$ 0.27 / LB
30000003	2230	1005462		NOV	2019	666,426 LB	$ 144,947.66	583,974 LB	$ 127,014.34	583,974 LB	$ 127,014.24	$ -17,933.42	$ 0.22 / LB
30000003	2230	1005462		DEC	2019	2,828,000 LB	$ 615,090.00	505,784 LB	$ 110,008.03	505,784 LB	$ 104,473.99	$ -510,616.01	$ 0.21 / LB
30000005	2230	1000562		DEC	2017	148,490 LB	$ 39,958.66	84,000 LB	$ 22,604.40	84,000 LB	$ 22,874.00	$ -17,084.66	$ 0.27 / LB
30000005	2230	1005074		NOV	2018	42,000 LB	$ 11,302.20	42,000 LB	$ 11,302.20	42,000 LB	$ 11,809.00	$ 506.80	$ 0.28 / LB
30000005	2230	1005074		APR	2019	106,490 LB	$ 29,465.78	84,000 LB	$ 23,242.80	84,000 LB	$ 23,618.00	$ -5,847.78	$ 0.28 / LB
30000005	2230	1005074		DEC	2019	148,490 LB	$ 42,572.08	0 LB	$ 0.00	0 LB	$ 0.00	$ -42,572.08	X
30000006	2230	1004428		AUG	2017	30,240 LB	$ 14,515.20	15,000 LB	$ 7,200.00	15,000 LB	$ 7,200.00	$ -7,315.20	$ 0.48 / LB
30000006	2230	1004428		NOV	2017	51,260 LB	$ 24,604.80	45,000 LB	$ 21,600.00	45,000 LB	$ 22,600.00	$ -2,004.80	$ 0.50 / LB
30000006	2230	1004428		NOV	2018	60,000 LB	$ 30,600.00	60,000 LB	$ 30,600.00	60,000 LB	$ 31,552.50	$ 952.50	$ 0.53 / LB

 

[alansidman]

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.

MS Excel: How to use the SUMIFS Function (WS)

This Excel tutorial explains how to use the Excel SUMIFS function with syntax and examples. The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

www.techonthenet.com

 

[aread]

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]

Also, if you sum G through M, aren't in doubling up on column I in column K?

 

[alansidman]

Actually, after looking it over a second time, I would create a pivot table to this.

Book1
	A	B	C	D	E	F	G	H	I	J	K
3	Material	Plant	Vendor	Fiscal year	Sum of PO quantity	Sum of Order value	Sum of Actual GR quantity	Sum of GR value	Sum of Invoiced quantity	Sum of Invoiced amount	Sum of Var Inv Val vs PO value
4	30000001
5		2230
6			1005462
7				2017	845075	250848.39	750559	223001.59	750559	217984.62	-32863.77
8				2018	784437	251920.04	720718	227987.51	720718	240716.15	-11203.89
9				2019	850000	266149.75	561657	180367.64	561657	169588.8	-96560.95
10	30000003
11		2230
12			1005462
13				2017	4086348	1119477.15	4034173	1104535.93	4034173	1079814.95	-39662.2
14				2018	3133420	942130.85	3091841	908593.52	3091841	930180.08	-11950.77
15				2019	5856330	1447773.26	3451662	924757.97	3451662	897726.11	-550047.15
16	30000005
17		2230
18			1000562
19				2017	148490	39958.66	84000	22604.4	84000	22874	-17084.66
20			1005074
21				2018	42000	11302.2	42000	11302.2	42000	11809	506.8
22				2019	254980	72037.86	84000	23242.8	84000	23618	-48419.86
23	30000006
24		2230
25			1004428
26				2017	81500	39120	60000	28800	60000	29800	-9320
27				2018	60000	30600	60000	30600	60000	31552.5	952.5
Sheet3

 

[DanteAmor]

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]

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]

Try the following:
The result will be in the "summary" sheet.
Change in the macro "Sheet1" and "Summary" for the names of your sheets.

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]

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"

 

[DanteAmor]

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.