I will try to say this gently, but if a formula does not give you the correct result, you do not have it right.
This formula
says: For every cell in $A2 that has a value of taxi, add the corresponding cell in the range B2:D2. However, A2 is just one cell, and A2 is "Taxi". So it will give you the first value in the range B2:D2, which is 1. Every other formula is doing the same thing, just a different row or column.
I can't tell you the correct formula unless you describe what you want the results to be. It's hard to guess from what is in this file. Also helpful to show an actual example of desired results.
My best guess is that for each row, you want to sum the numbers for the column headers that match the row header. So maybe this:
Values as displayed
|
A |
B |
C |
D |
E |
1 |
|
Taxi |
School |
Private |
|
2 |
Taxi |
1 |
2 |
3 |
1 |
3 |
School |
2 |
3 |
4 |
3 |
4 |
Private |
3 |
5 |
6 |
6 |
5 |
|
1 |
3 |
6 |
|
Underlying formulas
|
A |
B |
C |
D |
E |
1 |
|
Taxi |
School |
Private |
|
2 |
Taxi |
1 |
2 |
3 |
=SUMIF($B$1:$D$1,$A2,$B2:$D2) |
3 |
School |
2 |
3 |
4 |
=SUMIF($B$1:$D$1,$A3,$B3:$D3) |
4 |
Private |
3 |
5 |
6 |
=SUMIF($B$1:$D$1,$A4,$B4:$D4) |
5 |
|
=SUMIF($A$2:$A$4,B$1,B$2:B$4) |
=SUMIF($A$2:$A$4,C$1,C$2:C$4) |
=SUMIF($A$2:$A$4,D$1,D$2:D$4) |
|
Bookmarks