Lobsterboy1
Board Regular
- Joined
- Aug 5, 2016
- Messages
- 90
Hi,
I am trying to sum multiple rows for an index and match formula. I have tried sumifs but cannot get it to work. Here is a sample
What I am wanting is in H1 to look down H6 to H9 and make H6 to be worth 6 due to a Q been worth 6 for Dave, H7 to be worth 3 due to a W been worth 3 for Paul and so on. The out come I am trying to get to is H1= 18, I1=26, J1=26, K1= 26 and L1=23. Not all rows will have a letter in each column. Am I using the wrong thing with index and match. Thanks for any help.
I am trying to sum multiple rows for an index and match formula. I have tried sumifs but cannot get it to work. Here is a sample
Book1 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | 6 | 4 | 12 | 12 | 2 | |||||||||
2 | ||||||||||||||
3 | ||||||||||||||
4 | M | T | W | TH | F | |||||||||
5 | PERSON | W | M | Q | Y | E | P | 04 | 05 | 06 | 07 | 08 | ||
6 | Dave | 2 | 4 | 6 | 12 | 12 | Q | M | E | Y | W | |||
7 | Paul | 3 | 5 | 6 | 12 | 12 | W | Q | W | M | W | |||
8 | John | 4 | 6 | 6 | 12 | 12 | W | W | M | W | Y | |||
9 | Sarah | 5 | 7 | 6 | 12 | 12 | W | Y | W | W | Q | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H1:L1 | H1 | =INDEX($B$6:$F$6,MATCH(H6,$B$5:$F$5,0)) |
What I am wanting is in H1 to look down H6 to H9 and make H6 to be worth 6 due to a Q been worth 6 for Dave, H7 to be worth 3 due to a W been worth 3 for Paul and so on. The out come I am trying to get to is H1= 18, I1=26, J1=26, K1= 26 and L1=23. Not all rows will have a letter in each column. Am I using the wrong thing with index and match. Thanks for any help.