|Excel And SQL .com|
Home > VBA Macros
How to count sum values in one column based on values in another column
Let us you have a list of products in column A and their sales figures in column B. If you want to get the sum all all sales of your product, lets say Apples, you can get it using Excel formula below.
To do the same using VBA, you can use below code
Sub Demo() Dim arrayProducts() As String Dim arraySales() As String 'Initialize some data to work with arrayProducts = Split("Product:Apples:Mangoes:Berries:Apples:Bananas:Apples", ":") arraySales = Split("Sales:10:15:20:30:40:50", ":") Range("A1:A7").Cells.Value = Application.Transpose(arrayProducts) Range("B1:B7").Cells.Value = Application.Transpose(arraySales) 'clear the target cell first Cells(2, "E") = "" 'now populate E2 with the total amount of sales sales of Apples Cells(2, "E") = Application.WorksheetFunction.SumIfs(Columns("B"), Columns("A"), "Apples") End Sub
|© All Rights Reserved. ExcelAndSql.com|