I'm trying to make a sumifs with an array formula using sumif like this
=arrayformula(if(len(B2:B)=0;;sumif(DATOS!B:B&DATOS!S:S&DATOS!A:A;B3:B&T2&11;DATOS!G:G)))
The formula works fine except when the condition uses another kind of operators like >0 example
=arrayformula(if(len(B2:B)=0;;sumif(DATOS!B:B&DATOS!S:S&DATOS!A:A;B3:B&T2&">0";DATOS!G:G)))
I always get a 0 as a result. I was wondering what was the syntax error. I want to use comparison operators but I don't find the answer.
Sample data
My objective is to calculate de quantity sold by-product on L1 to n4. Headers 1,2,3 represent months. To make the calculation I have to make a sumifs of the units sold of that product on a given month. I also want to sum only positive quantities and being able to change the calculation depending on the shop. You can change the shop on I9 with a validation cell that contains all centers and a special selection with all.
Try the following array formula. See your sheet tab harun24hr
.
=MAKEARRAY(ROWS(I2:J4);COLUMNS(L1:N1);
LAMBDA(r;c;SUMIFS(E2:E;
B2:B;INDEX(I2:J4;r;1);
C2:C;INDEX(I2:J4;r;2);
F2:F;c;
E2:E;">0";
A2:A;I9)))