VBA rounding before Sumproduct (with named column range)

[SevenDP]

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

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

 

[FormR]

all done in 14 hours, before it was done in 22 hours (60k++ row & 81 column)

Hi, I'd suggest that you seriously consider a total re-think, Excel is not really designed for churning data for 14+ hours (personally I'd abandon any approach using Excel if it was taking more than a few minutes!). Maybe you need to investigate if there are better/more appropriate tools for the job.

 

[SevenDP]

Hi, I'd suggest that you seriously consider a total re-think, Excel is not really designed for churning data for 14+ hours (personally I'd abandon any approach using Excel if it was taking more than a few minutes!). Maybe you need to investigate if there are better/more appropriate tools for the job.

Yes, we planning to change this excel macro to a PHP MYSQL application, but it needs time (coding priority in my office, maybe can afford it in next year)
and as long as the application not ready, we will still using this macro
the macro creator already resigned and now I maintenance it

thanks sir