Sumif with Array Formula + "GREATER THAN"

Question

I know how to use sumif with array formula in this way: =ARRAYFORMULA(sumif(F3:F10,A3:A6,J3:I10))

But when I try to use greater than or less than signs, it doesn't work: =ARRAYFORMULA(sumif(F3:F10&H3:H10&I3:I10,A3:A6&">"&B3:B6&"<"&B3:B6,J3:J10))

Sample: https://docs.google.com/spreadsheets/d/1-YaMwpq9ft3nBVwPkDOjj-L42xPtUN1gE0JZO6pS2xY/edit?usp=sharing

The purpose is to sum amount of fruits given the max amount, min amount, fruit type criteria. Tried to use sumifs, it doesn't work.

Answer 1

If you don't necessarily need to use SUMIF, you may try with QUERY instead:

Sample Formula (Paste it on C3)

=QUERY(F3:J10, ("select J where F = '" & A3 & "' AND " & B3 & " >= H AND " & B3 & " <= I"))

References:

• QUERY function
• Google Query Syntax

Answer 2

try:

=ARRAYFORMULA(VLOOKUP(A3:A6&B3:B6, {F:F&G:G, J:J}, 2, 0))

or:

=ARRAYFORMULA(VLOOKUP(A3:A6&TEXT(B3:B6, "00000"), 
 SORT({F:F&FILTER(TEXT(H:H, "00000"), H:H<>""), J:J}), 2, 1))