Lookup a number that will occur more than once in one vector, then create a sum

Question

Lookup a number that will occur more than once in one vector, then create a sum from another vector based on the locations of that number. All on one row.

I have a database. Each row has two vectors. I want to look up the position of a number in one vector (it will occur more than once) and then use the location of each occurrence in the first vector to sum numbers in the second vector in the same location. Each vector is the same length...

Example.  A1:E1 has these entries: 4, 3, 4, 5, 4     and G1:K1 has these entries: 0, 1, 1, -1, 0.

I would like cell M1 to have a formula that would look up each occurrence of the number 4 in A1:E1  and then sum the number in G1:K1 that matched the location of each 4 in A1:E1. In the example above the formula would return 1 because the number 4 occurred in the 1st, 3rd, and 5th position of the A1:E1 vector, and the 1st, 3rd, and 5th position of the G1:K1 vector is 0, 1, 0 so the Sum of that is 1.

Thanks !!

Explanation / Answer

Hi..

An array formula will do this :

Given your example data, and putting the value 4 in M1, then put the following array-entered formula in N1

=SUM((A1:E1=M1)*(G1:K1)) and press CTRL+SHIFT+ENTER to enter it as an array.

to test, changing the value in M1 to 5 should give the value -1 in N1..

Regards..

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.