SUM function not functioning

[akaron]

Hi, I am able to assemble a cell range (O21:O162) using this formula:
=CONCATENATE("O21:O",ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670)))

But when I insert it into a sum function, I get a #VALUE! error. Can someone show me what I'm doing wrong?
=SUM(CONCATENATE("O21:O",ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670))))

I just wanted to find the sum of all values in the range I put together (O21:O162).

 

[RobP]

Hi,

Try using INDIRECT

=SUM(INDIRECT("O21:O162")

Rgds
Rob

 

[akaron]

When I use:
=SUM(INDIRECT(CONCATENATE("O21:O",ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670)))))
it comes up with ($276.13)

If I use:
=SUM(O21:O162)
I get $247.54

I know that this function is giving me O21:O162, because I have it in another cell to check
=CONCATENATE("O21:O",ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670)))

Why would they be different sums just with the addition of INDIRECT?

 

[Fluff]

How about

=SUM(O21:INDEX(O:O,ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670))))

 

[shift-del]

Hi

Try this:

=SUM(O21:INDEX(O:O,XMATCH(-9^99,B21:B45,1)+20))

 

[akaron]

How about

=SUM(O21:INDEX(O:O,ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670))))

This is the same incorrect sum (???)

I lied! this generates the correct sum, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.