Help Me

[suhardi]

I have table as below:

Name	Day	1	2	3	4
A	Value	10	10	10	10
A	Category	X	Z	Z	Y
B	Value	5	5	5	5
B	Category	Y	X	Z	Y
C	Value	15	15	15	15
C	Category	Z	X	Z	Y

<tbody>
</tbody>

then, want to write a formula to sum the value of each category each day
so i get:

Day	X	Y	Z
1	10	5	15
2	20	0	10
3	0	0	30
4	0	30	0

<tbody>
</tbody>

Please help me to solve this
Thanks for your help

 

[VBA Geek]

in K2

=SUMPRODUCT(N(INDEX($C$3:$F$8,0,MATCH($J2,$C$1:$F$1,0))=K$1),INDEX($C$2:$F$7,0,MATCH($J2,$C$1:$F$1,0)))

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Name</th><th>Day</th><th>1</th><th>2</th><th>3</th><th>4</th><th> </th><th> </th><th> </th><th>Day</th><th>X</th><th>Y</th><th>Z</th></tr>
<tr><td>A</td><td>Value</td><td>10</td><td>10</td><td>10</td><td>10</td><td> </td><td> </td><td> </td><td>1</td><td>10</td><td>5</td><td>15</td></tr>
<tr><td>A</td><td>Category</td><td>X</td><td>Z</td><td>Z</td><td>Y</td><td> </td><td> </td><td> </td><td>2</td><td>20</td><td>0</td><td>10</td></tr>
<tr><td>B</td><td>Value</td><td>5</td><td>5</td><td>5</td><td>5</td><td> </td><td> </td><td> </td><td>3</td><td>0</td><td>0</td><td>30</td></tr>
<tr><td>B</td><td>Category</td><td>Y</td><td>X</td><td>Z</td><td>Y</td><td> </td><td> </td><td> </td><td>4</td><td>0</td><td>30</td><td>0</td></tr>
<tr><td>C</td><td>Value</td><td>15</td><td>15</td><td>15</td><td>15</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>C</td><td>Category</td><td>Z</td><td>X</td><td>Z</td><td>Y</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td></td></tr>
</table>