SUMPRODUCT - multiply/ lookup - problems with array

[tbablue]

=SUMPRODUCT((LEFT(Input_Table[[LINE TYPE]:[LINE TYPE]],4)="_RES")*(Input_Table[[INCLUDE]:[INCLUDE]]=1)*(SUBTOTAL(109,OFFSET(AG58,ROW(Input_Table[22/07/24])-MIN(ROW(Input_Table[22/07/24])),0))))

I need to multiply each result by a corresponding lookup value associated with the values in Input_Table[CAT}.

The indexing values are found in AC5;AC38. The values to be returned are found in AG5:AG38.

Does this make sense

 

[Kaper]

Not seeing data and expected result, I'd expect something like:
=SUMPRODUCT(XLOOKUP(Input_Table[[CAT]:[CAT]],$AC$5:$AC$38,$AG$5:$AG$38,,0)*(LEFT(Input_Table[[LINE TYPE]:[LINE TYPE]],4)="_RES")....
would do the job

 

[tbablue]

Swapped one error for another. I get a #VALUE error now.

=SUMPRODUCT(XLOOKUP(Input_Table[[CAT]:[CAT]],$AC$5:$AC$38,AE$5:AE$38,,0)*(LEFT(Input_Table[[LINE TYPE]:[LINE TYPE]],4)="_RES")*(Input_Table[[INCLUDE]:[INCLUDE]]=1)*(SUBTOTAL(109,OFFSET(AE58,ROW(Input_Table[08/07/24])-MIN(ROW(Input_Table[08/07/24])),0))))

 

[Kaper]

Swapped one error for another. I get a #VALUE error now.

Was there an error before? What type?
It's not easy to debug not seeing data

 

[tbablue]

No problem friend. Really grateful that you took time. Thank you.

You did point me in a direction that delivered a working solution.

Not sure if there's a more optimal answer but ....

=SUMPRODUCT((LEFT(Input_Table[[LINE TYPE]:[LINE TYPE]],4)="_RES")*(Input_Table[[INCLUDE]:[INCLUDE]]=1)* INDEX(AD$5:AD$37, IFERROR(MATCH(Input_Table[[CAT]:[CAT]], CAT_LIST, 0),0))*(SUBTOTAL(109,OFFSET(AD58,ROW(Input_Table[01/07/24])-MIN(ROW(Input_Table[01/07/24])),0))))

... delivers expected results.

 

[RoryA]

FWIW if you can add a helper column using SUBTOTAL (or AGGREGATE) to indicate the hidden rows, your formulas will be more efficient as they won't have to use OFFSET. Since you have 365 you could also use FILTER and MAP.

 

[tbablue]

I recognise that OFFSET is volatile - but I can't have helper fields.

I tried to use AGGREGATE but struggled to implement it.