I have a Calc spreadsheet as follows:
| A | B | C | D | E
--------------------------------
1 | 0 | 4 | 5 | 0 |
2 | 5 | 6 | 9 | 8 |
3 | 4 | 9 | | |
4 | 9 | 3 | | |
I want to get in E the sum of elements in B whose corresponding cell in A has a value contained in C or D. For example, E1 should be 10 (i.e. B1+B2) since both A1 and A2 values are present in C1:D1. Similarly, E2 should be 3 (i.e. B4) since only A4 has a value in C2:D2. How can I obtain this?
Paste the following formula in E1 and fill down to E4 to produce 10 and 3.
=IF(C1<>"";SUMPRODUCT((A$1:A$4=C1)+(A$1:A$4=D1);B$1:B$4);"")
This works by getting an array of one value for each row that is true or false depending on whether each value in A exists in C or D of that row. If false, then it multiplies whatever is in that row by 0, effectively disposing of that row. If true, then the value in B is multiplied by 1. All non-zero results are added up.
Originally I tried using OR, but + is required instead because OR can only handle one row at a time.
The C1 check avoids trying to calculate the rows where C is empty.