2

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.

sample set

Zyloric
  • 23
  • 4
  • Use `SUMIFS()` with multiple conditions instead of making single condition by concatenating multiple columns. Use `BYROW()` or `MAP()` function to make it dynamic spill instead of legacy array approach. Better, share some sample data then desired output. You can also share a sample google-sheet file here. – Harun24hr Mar 30 '23 at 07:45
  • My objective is to calculate de quantity sold by-product on each month with the condition of only calculating the sum on non negative numbers and also being able to choose the calculation in all shops or one by one using a validation cell https://docs.google.com/spreadsheets/d/1FN2GYebC3ppp6VgwOoNBjsV7YDSY16A2nIxcxRzdv1Y/edit?usp=sharing – Zyloric Mar 30 '23 at 11:35
  • Could you add a sample of the desired output in the sheet that you shared? And maybe add the formula that you already created but using the structure of the sample sheet. – Bryan Monterrosa Mar 30 '23 at 14:26

1 Answers1

2

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)))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36