Google Sheets - not getting desired result using SUMIFS, QUERY or ARRAYFORMULA

Question

I have details of meals ordered in the "Meal Order Sheet". I wish to sum the number of Add-on orders in "Meal Order Sheet"!J: J in another sheet for items listed in the column "Meal Order Sheet"!I:I. I used SUMIFS, QUERY formula, and ARRAYFORMULA and I am not getting the desired results.

Sample "Meal Order Sheet"!Cx:Mx

=sumifs('Meal Order Sheet'!J:J,'Meal Order Sheet'!I:I,I3, 'Meal Order Sheet'!C:C,$F$1,'Meal Order Sheet'!L:L,$B$2)

I am always getting a result "0" (Zero).

Using either of these

=QUERY('Meal Order Sheet'!A:L, "SELECT SUM(J) WHERE I = '"&I3&"' AND C = '"&$F$1&"' AND L = '"&$B$2&"'", 1)

=ARRAYFORMULA(QUERY('Meal Order Sheet'!A:L, "SELECT SUM(J) WHERE I = '"&I3&"' AND C = '"&$F$1&"' AND L = '"&$B$2&"'", 1))

I always get the header in cell 'Meal Order Sheet'!J1, which is "No of Add-ons".

Using formula

=QUERY('Meal Order Sheet'!A:L, "SELECT SUM(J) WHERE I = '"&I3&"' AND C = '"&$F$1&"' AND L = '"&$B$2&"'", 0)

I am always getting result "SUM".

What is the correct formula that I should use?

Answer 1

You may try:

=QUERY('Meal Order Sheet'!A:L, "SELECT sum(J) WHERE I = '"&I3&"' AND C = date '"&text(F1,"yyyy-mm-dd")&"' AND lower(L) = lower('"&$B$2&"') label sum(J) ''", 0)