VBA rounding before Sumproduct (with named column range)

SevenDP

New Member
Joined
Mar 10, 2016
Messages
21
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

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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"

Hi, you could try like this;

Code:
nilai = Evaluate("sum(if(data[key]=""A"",round(data[value],0)))")
 
Upvote 0
Hi @FormR
Sorry i want to ask again, last code sometimes give me an error : run time error 1004 the item with the specified name wasn't found
Code:
ttl_m3 = Evaluate("sum(if(DATA[KEY]=""PLH"",round(DATA[TTL_M3],2)))")

this is the original codes before i change it
Code:
    ttl_m3 = 0
    For Each c In Sheets("DATA").Range("DATA[KEY]")
        If c.Value = "PLH" Then
            ttl_m3 = ttl_m3 + Round(Sheets("DATA").Cells(c.Row, 25).Value, 2)
        End If
    Next c

because it was too slow, then i change that to this codes

Code:
ttl_m3 = Round(WorksheetFunction.SumIf(Sheets("DATA").Range("DATA[KEY]"), "PLH", Sheets("DATA").Range("DATA[TTL_M3]")), 2)

but i need to round each of them first then sum them

thanks in advance
 
Upvote 0
last code sometimes give me an error : run time error 1004 the item with the specified name wasn't found

Hi, are you sure that it is the "ttlm3 = evaluate(..)" line of code that is generating the error?
 
Upvote 0
Hi, are you sure that it is the "ttlm3 = evaluate(..)" line of code that is generating the error?

Yes, Sir
That line have yellow highlighted when error occurred and i click "Debug" button

Like this

96b80b51da6e042d2403f5ec0c76b6ac-full.png


Thanks Sir
 
Upvote 0
I'm not sure what the problem is to be honest.

Like this

Maybe you could upload a sample workbook that demonstrates the problem to a file sharing site, like drop dox, and share the link here.
 
Upvote 0
Sir, when i try type this in one cell

Code:
=SUM(IF(DATA[KEY]="PLH";DATA[TTL_M3]))

got error #VALUE
but if i Ctrl+Shift+Enter on it
it show the correct result

Can that thing make the problem clear?

if not, i will try to make sample of this workbook
need time because it was not me create this macro (pretty complex for me, lol)
now i just maintenance it

thanks
 
Upvote 0
Sir, when i try type this in one cell

Code:
=SUM(IF(DATA[KEY]="PLH";DATA[TTL_M3]))

got error #VALUE
but if i Ctrl+Shift+Enter on it
it show the correct result

That behavior is expected, the formula needs array entry when used directly in the worksheet. This is not the cause of the problem.

need time because it was not me create this macro (pretty complex for me, lol)

Surely you only need to include the (sanitised) data and the single line of code that is causing the error?

Or are you saying that the single line of code works when used in isolation?
 
Upvote 0
Hi,
I don't know, after I restart the computer, then run the macro again before i go home
then this morning the error is gone, all done in 14 hours, before it was done in 22 hours (60k++ row & 81 column) got too many for each lines
now I need compare the result

btw this is the sample of my workbook with dummy data

https://drive.google.com/file/d/1wx7qCHg5aYle77vs475Qp3VoHy_p1HAw/view?usp=sharing

sometimes other scripts have strange behavior too, like can not pass "on error resume next" line, fix after restart the computer.

thanks sir
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top