I have a table where in columns A and B have fixed information and C through E (and beyond) is given values and characters.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Index | Factor | Day1 | Day2 | Day3 |
| 2 | 100 | 100 | 3 | 1 | 2 |
| 3 | 200 | 50 | 3 | XX | |
| 4 | 300 | 250 | 2 | X | |
| 5 | 400 | 50 | 2 | 3 | |
| 6 | 500 | 30 | XX | XXX | 3 |
| 7 | 600 | 15 | X |
What I want to do is to use a SUMPRODUCT formula to multiply the values in the “Day” columns by the “Factor” column. If the value in a cell is a number, it makes sense, but what I want to do is also include the cells that have “X”‘s.
If a cell has “X”, it is a 1. If a cell has an “XX”, it is a 2. And so on.
I have tried the SUMPRODUCT formula over the ranges, which give me the value of the numbered cells in the “Day” columns by the “Factor” column, added together. As an example, here is the formula for the total of column C:
=SUMPRODUCT(C2:C7,$B$2:$B$7)
Which returns 800. If I were to consider the “XX” in the column to be 2, that would bring the SUMPRODUCT to 860.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Index | Factor | Day1 | Day2 | Day3 |
| 2 | 100 | 100 | 3 | 1 | 2 |
| 3 | 200 | 50 | 3 | XX | |
| 4 | 300 | 250 | 2 | X | |
| 5 | 400 | 50 | 2 | 3 | |
| 6 | 500 | 30 | XX | XXX | 3 |
| 7 | 600 | 15 | X | ||
| 8 | Only SUMPRODUCT | N/A | 800 | 350 | 440 |
| 9 | SUMPRODUCT While Considering “X”s | N/A | 860 | 690 | 555 |
How would I modify the formula to include the “X”s?