Excel Formula Help in Numbers

[sgt_hoser]

I have a formula that works great in Excel, and that I have not been able to re-write it to work in Numbers. Here it is.

=IF(LEN(B2)>0,SUM(A3:INDIRECT("A"&ROW()+IFERROR(MATCH("*$",A3:A52,0),50))),"")

I realize that everone one of these individual formulas are supported in Numbers, it seems to be the nested INDIRECT portion causing the grief. When I try to open this in Numbers I get a message saying the formula is not supported, and it has pasted the last values.

Is there a way to write this formua to to work in both Excel and Numbers?

If you are curious, this formula is writen to SUM a variable range of values between BarCodes (containing the $). The value string that needs to be summed can be as short as 1 value between each bar code, and up to 15-20 rows or values between.

Any Ideas?

Hoser

 

[twerppoet]

Off the top of my head I see one problem. The SUM function can only take one variable (sort of); either a range or column/row. To make the INDIRECT function work inside it you'll have to generate the pointer to the entire address, not just half of it. I also suspect that the stuff going on inside the INDIRECT statement will have to be done differently.

As for the rest, I really don't understand what you are doing well enough to guess at a workable formula. If you can supply a sample sheet I might be able to come up with something.