Erlandsen Data ConsultingIt’s not possible to add the values in a range depending on each cells background color with the built-in SUM()-function. With the custom function below you can add the contents of cells depending on their background color: Function SumByColor(InputRange As Range, ColorRange As Range) As Double ‘ returns the sum of each cell in the [...]
It’s possible to add values from a range in another worksheet like this: =SUM(Sheet1!A1:A100) This formula could then be copied to the other sheets in the workbook, but you would have to edit all the sheet names manually. With the user defined functions below you can enter the function once (e.g. if you group the [...]
If the range A1:A1000 contains values and several subtotals, you probably will have some difficulties to add the grand total of the values in this range. One easy way to find the grand total is by using the formula below: =SUM(A1:A1000)/2 This solution assumes that all the subtotals adds all the items in the range. [...]
It’s possible to achieve this with the built-in SUM()-function, but that can be a big task if the workbook contains many worksheets, or if you frequently add new worksheets to the workbook. To add a range from several worksheets you can use the SUM-function like this: =SUM(Sheet1:Sheet10!A1:A100) The formula above will add the range A1:A100 [...]
The custom function below adds the unique values only from a given range: Function SumUniqueValues(InputRange As Range) as Double Dim cl As Range, UniqueValues As New Collection, uValue As Variant Application.Volatile On Error Resume Next For Each cl In InputRange UniqueValues.Add cl.Value, CStr(cl.Value) Next cl On Error GoTo 0 SumUniqueValues = 0 For Each uValue [...]
With the worksheetfunction SUMIF() it’s possible to return the sum of all cells in a given range that fulfills a given criteria. The function =SUMIF(A1:A100,">1000") will return the sum of all values in the range A1:A100 that is greater than 1000. The function =SUMIF(A1:A100,">1000",B1:B100) will return the sum of all values in the range B1:B100 [...]
A semicolon (;) is used in all examples as the list separator, replace it with a comma (,) if necessary. Description Formula Add the numbers in E4:E23 =SUM(E4:E23) Add the numbers in G4:G26. Ignores the result from other SUBTOTAL-functions in the range. Includes visible cells only. =SUBTOTAL(9;G4:G26) Add the numbers in E4:E23 if they are [...]