biomathics23
New Member
- Joined
- Sep 10, 2018
- Messages
- 6
Hello and thanks for reading,
I have a setup similar to the following (but much larger), featuring a single column containing names (each cell in this column will have a name), and several columns whose cells may contain letters (though many do not). Each name is unique but more than one letter may appear in a single cell as follows:
<tbody>
</tbody>
I am trying to find a formula that captures the following notion: Given Xerxes and A, how many rows have a "1" in the Numbers column and also have at least one "A" somewhere in Letters1-4 columns? So for Xerxes and A, only row 3 satisfies that, so the formula should return "1". For Yanni and A, rows 1 and 5 work, so the formula should return "2", etc.
I would like to use this formula to then populate a grid like the one below (results shown are based on the above example):
<tbody>
</tbody>
I tried to manipulate the sumproduct and countifs functions in a variety of ways, but I could never figure out how to get it to look at several cells (in actuality 10+) in a single row for a given letter while still summing over all the rows. The only solution I could think of would be to add a helper column of concatenated columns Letters1-4, and then sumproduct or countifs with that helper column and the name column, but I would vastly prefer avoiding such a workaround.
Any thoughts/advice/solutions?
I have a setup similar to the following (but much larger), featuring a single column containing names (each cell in this column will have a name), and several columns whose cells may contain letters (though many do not). Each name is unique but more than one letter may appear in a single cell as follows:
Row | Names | Letters1 | Letters2 | Letters3 | Letters4 |
1 | Yanni | A,B | A | A, C | |
2 | Zane | ||||
3 | Xerxes | B | A, B | ||
4 | Yanni | C | C | ||
5 | Yanni | A | |||
6 | Xerxes | B,C | |||
7 | Zane | A | B | C |
<tbody>
</tbody>
I am trying to find a formula that captures the following notion: Given Xerxes and A, how many rows have a "1" in the Numbers column and also have at least one "A" somewhere in Letters1-4 columns? So for Xerxes and A, only row 3 satisfies that, so the formula should return "1". For Yanni and A, rows 1 and 5 work, so the formula should return "2", etc.
I would like to use this formula to then populate a grid like the one below (results shown are based on the above example):
A | B | C | |
Xerxes | 1 | 2 | 1 |
Yanni | 2 | 1 | 2 |
Zane | 1 | 1 | 1 |
<tbody>
</tbody>
I tried to manipulate the sumproduct and countifs functions in a variety of ways, but I could never figure out how to get it to look at several cells (in actuality 10+) in a single row for a given letter while still summing over all the rows. The only solution I could think of would be to add a helper column of concatenated columns Letters1-4, and then sumproduct or countifs with that helper column and the name column, but I would vastly prefer avoiding such a workaround.
Any thoughts/advice/solutions?