Conditionally divide two arrays

Dear Forum,

I have the following issue. I would like to conditionally divide two arrays by one another and sum the result. In the example below, I want to divide row 2 by row 4 but only if the word in row 1 appears in row 3. Here, this yields: 1) divide cell A2 by B4 (because both have the mathcing word "word1") which gives 2 and divide cell C2 by C4 (both have the matching word "word3") which gives 3. 2) Sum the result which gives 5.

Thank you very much for your help! very appreaciated!
Best regards

	a	b	c	d	e	f
1	Word1	Word2	Word3	Word4	Word5	Word6
2	10	13	18	15	11	19
3	Word8	Word1	Word3	Word9
4	5	5	4	4

(both have the matching word "word3") which gives 3. 2)

Word3 has 18 in A2:F2 and 4 in A4:F4. 18/4 = 4.5 which would result in sum({10;18}/{5;4}) = sum({2;4.5}) = 6.5

If that is not what you expect I will need additional detail.

In the meantime this cumbersome beast returns 6.5 and references a helper cell H1.

Formula:

Please Login or Register  to view this content.

Then this array entered. (Perhaps this can be shortened but I want to be clear on the concept first.)

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

This all depends upon the numbers being sorted ascending which is not going to be the case I'm sure.

Removed by FR.

Am I missing something or can you just use:

=SUM(IFERROR(A2:F2/SUMIF(A3:D3,A1:F1,A4:D4),0))

with CSE?

Regards

Yup! Much better!!