Help with formula pls

Hi all, I’m having problems when trying to make an arrayformula from a SUMIFS formula that’s currently working, any ideas?


(This is the one working)


(This is what happens with arrayformula, it seems to refer only to the first row? how can I fix that?)

SUMIFS is not supported with ARRAYFORMULA.

I guess you should find alternatives or workarounds thanks to Google …

Can you tell me in English what do the notations in the formula mean?

You may also have a look at my script in this post to copy the formula down.

I’m also noticing that your formula has A2 instead of the array A2:A. Not sure if that would help as like @Christophe_HK said, SUMIFS doesn’t work with array formulas. Only SUMIF does. You may be able to use SUMIF if you join column values together.

Sure thing, they submit a form to ‘Completados’ pending approval, when admin approves Completados!H2:H,TRUE() checks if the email of the submitted form matches the email of the user Completados!B2:B,A2, and then sums the exp coming from all the submitted and approved forms Completados!I2:I

Gonna check your tutorial :slight_smile:

1 Like

Yeah, also tried that but same happens :frowning:

It should work with the script and a trigger to copy it down. Hopefully my tutorial would help.

Use SUMIF.

2 Likes

I don’t know if this was exactly how you meant but I fixed it with two separate formulas, one checking each condition and now it works with an arrayformula :smiley:

Edit: that didn’t work either, was working whether it was approved or not, but I think I could make it with this formula:

ARRAYFORMULA(IF(LEN(A2:A),SUMIF(Completados!B2:B&Completados!H2:H,A2:A&TRUE,Completados!I2:I)))

Thanks everyone!

P.s. @ThinhDinh thanks a lot for the tutorial but scripting seems to be above my level for now, I was still trying to understand the way it works :smiley:

2 Likes

Yeah that concatenating “&” inside should work, well done!

1 Like