Array formulae with different sized array, e.g. array-if() or somproduct-if()?

[hlhans]

Dear All,

I am confused and honestly not so sure, whether Excel could achieve what I want, i.e. a sort of "array-if-formula":
In the end I want to express a “sumproduct” of two arrays that do not have the same size in the first place. One is bigger than the other but once my criteria is matched they consist of exactly the same number of elements that need to be multiplied with with each other and summed up in the end.
Please find in the illustrative example those arrays and different ways to achieve what I want. The first approach achieves my goal literally step by step and the two others are a little bit more straight to the point. You see, I can meet my goal with the help of intermediate step(s) but in the end I would like to get there within ONE step, even though that step might be a little more complicated.
The current attempts to do this are in the red Frame: Unfortunaelty neither the use of index() nor vlookup() is sufficient within an array-formula as such, because it seems that these two formulae do not support the array-idea in general. Both of them do multiply the elements of the other smaller array with the first figure of the bigger one, only (pls compare: “X-Check of the approach in question”.

I would appreciate your support, even if the final outcome might be that excel cannot do this...!

Thanks a lot in advance and kind regards from Hamburg


HLHans

[loginjmor]

Hi -

I don't think you can do what you're attempting with a formula. I think you would have to use VBA programming to do it in one cell.

[jason.b75]

This works with your sample, but would be too prone to error to be considered reliable for general use.

The SUBTOTAL section of the formula effectively cleans up Array 1 by eliminating the elements not found in array 2, The remaining elements in Array 1 must be in the same order as in array 2, if the order is different, or any elements in Array 2 don't match up with Array 1 then the formula will fail.

{=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1,SMALL(IF(FREQUENCY(IF(A6:A14=TRANSPOSE(A26:A31),ROW(A6:A14)),ROW(A6:A14)),ROW(A6:A14)),ROW(A26:A31)-ROW(A26)+1)-1,0,1,1)),B26:B31)}

For reliability, I would go with Approach 2, or, as previously suggested, a vba approach.

[hlhans]

Jason, in case it didn't come through the frist time: THANKS A LOT!!! That helped a lot und is much appreciated. Kind regards from Hamburg
HLHans