round sum_range of arrayformula

32 views Asked by At

Here is my table

Column A Column B
A 1
A 2
B -1.7148
B 1.3454
B .3694

I am trying to use a singular formula to sum the totals of Col B based on the Values of Col A rounded to 2 digits

=ARRAYFORMULA(sumif(a:a,unique(a:a),b:b))

results in

Current Result
3
0

The issue here is I need these columns rounded before they are summed in order to get this result

Want Result
3
.01

Logically it made sense to adjust my formula to..

=ARRAYFORMULA(sumif(a:a,unique(a:a),round(b:b,2)))

but that gives an error -- "Argument must be a range."

Are there any workarounds I can use here?

2

There are 2 answers

0
rockinfreakshow On BEST ANSWER

You may try:

=map(unique(tocol(A:A,1)),lambda(Σ,sum(ifna(filter(round(B:B,2),A:A=Σ)))))

enter image description here

0
z.. On

Another solution:

=MAP(UNIQUE(TOCOL(A2:A,1)),LAMBDA(x,SUMPRODUCT(ROUND(B2:B,2),x=A2:A)))