SUM(COLLECT) Stopped working

Cidniz

Hello Community,

I have been using this formula for years now

=(SUM(COLLECT({5.A Invoice Log ASPR Range GR Amount}, {5.A Invoice Log ASPR PO#}, =[PO#]@row ))) + (SUM(COLLECT({5.B Invoice Log Archive ASPR GR Amount}, {5.B Invoice Log Archive ASPR PO#}, =[PO#]@row )))

Somewhere in the last couple of days though it stopped collecting. I have tried to find the error by just running a Collect function on either of the 2 data sets and it keeps coming up with 0.00, even though there is data on the sheet. If I just go to collect one single data set it gives me #Invalid Column Value, even though all columns used in this formula are Text/Number.

Any help would be greatly appreciated.

jmyzk_cloudsmart_jp

Hi @Cidniz

A possible reason is that the ranges in the form have been somehow changed.

If you are summing the GR Amount in the first sheet, the column should show right-oriented small triangles.

As shown above, your PO# and GR Amount columns have no triangles.

In my demo solution, the GR Amount and PO # columns have outbound arrows, as they are referenced in the GR Amount formula in the second sheet.

In your second sheet, the GR Amount column has inbound triangles.

Your second sheet's GR Amount column does have the inbound triangles. So, I guess, the formula references some ranges, but not the intended ones.

If I delete a range, the formula will give the #INVALID REF errors, but yours are showing no error, but zeros. So, I think someone changed the ranges, which led your formula to reference non-indented columns.

Another rare possibility is that the GR Amount inputs are TEXT, like, '="$12,095.00".

In the demo above, PO #: 4525001644 has a text value, so the formula returns zero.

But I think that case is unlikely.

So, I suggest checking the ranges in the formula.