Hi guys
I have data in 2 named column range (real data was 81 column & growing 60.000++ row data)
name "key" in range column A:A (string)
name "value" in range column B:B (decimal)
data example:
key, value
A, 10,212
A, 12,990
B, 11,119
B, 9,888
C, 2,911
i want to round every value first, then sum them
but i cant use sum(round(range)) -> error
so my approach was
those codes were work but without rounding every value first.
my question:
1. how to add round column B first before sum them, but please do not add new column for round column b
2. how to adopt named column range so no need using Range("A2:A60000") or Range("B2:B60000") but with "key" and "value"
thanks a lot
I have data in 2 named column range (real data was 81 column & growing 60.000++ row data)
name "key" in range column A:A (string)
name "value" in range column B:B (decimal)
data example:
key, value
A, 10,212
A, 12,990
B, 11,119
B, 9,888
C, 2,911
i want to round every value first, then sum them
but i cant use sum(round(range)) -> error
so my approach was
Code:
Dim nilai as variant
Dim wb As Workbook
Set wb = ThisWorkbook
nilai = Application.Evaluate("SUMPRODUCT(--(" & wb.Worksheets("DATA").Range("A2:A60000").Address & "=""A"")," & _
wb.Worksheets("DATA").Range("B2:B60000").Address & ")")
OR
nilai = WorksheetFunction.SumIf(Sheets("DATA").Range("DATA[key]"), "A", Sheets("DATA").Range("DATA[value]"))
those codes were work but without rounding every value first.
my question:
1. how to add round column B first before sum them, but please do not add new column for round column b
2. how to adopt named column range so no need using Range("A2:A60000") or Range("B2:B60000") but with "key" and "value"
thanks a lot