how to use sumproduct subtotal with criteria in another sheet. on google sheet

hallo sir , im new in this forum. and sorry for my bad english.

i have problem how to use sumproduct subtotal with criteria in another sheet. on google sheet
with formula =SUMPRODUCT(SUBTOTAL(109,OFFSET(MATRIAL!I6,ROW(MATRIAL!I6:I538)-ROW(MATRIAL!I6:I538),0)),--(MATRIAL!J6:J538=F22))

pm me email i will share copy spreadsheet

should be

=SUMPRODUCT(SUBTOTAL(109,OFFSET(MATRIAL!I6,ROW(MATRIAL!I6:I538)-ROW(MATRIAL!I6),0)),--(MATRIAL!J6:J538=F22))

to explain

=SUMPRODUCT(SUBTOTAL(109,OFFSET(A1:A10,ROW(A1:A10)-ROW(A1:A10),0)),--(B1:B10="apple"))

in the above - the SUBTOTAL is looking at A1, 10 times, as the ROW array is just returning ten 0s (1-1, 2-2, 3-3, 4-4 etc) --- then multiplying A1 by however many times the value in B1:B10 = "apple"

conversely

=SUMPRODUCT(SUBTOTAL(109,OFFSET(A1:A10,ROW(A1:A10-ROW(A1),0)),--(B1:B10="apple"))

will return a row array of 0 to 9 (1-1,2-1,3-1,4-1 etc) -- and so use A1:A10 values relative to whether the equivalent value in B is "apple"

HTH

thank you very much sir for helping me.
I've also finished with sumif with the addition of a helper column in the "matrial sheet". but I will try with the formula you made, because it is easier to understand.
Attachment 766626

looking deeper at the spreadsheet, there seems to be several issues with the mathematical logic.

for example...

F9:

Please Login or Register  to view this content.

why is there sum of a single cell?
why not simply:

Please Login or Register  to view this content.

then in F14:

Please Login or Register  to view this content.

but you have already included MATRIAL in F12:

Please Login or Register  to view this content.

which means you are adding things repeatedly, for what benefit/reason?