Hi,
I am currently trying using a table in Sheet1 (similar to whats below) to summarize the data on Sheet 2 which has data over more than one line. The formula i am using is
=IFERROR(INDEX(Sheet2!$B$2:$D$400,MATCH(1,(Sheet2!$B$2:$B$400=Sheet1!$C$5)*(Sheet2!$C$2:$C$400=Sheet1!B6),0),3),0)
<tbody>
</tbody>
Sheet 2 looks similar to this:
<tbody>
</tbody>
My problem however is that the formula is just picking up the first number (39 for English Complete pack). I would like the formula to Sum 39+200-20 for English Complete pack.
Please let me know how i can fix this.
Thanks (i would upload the excel if i could figure out from where :S)
I am currently trying using a table in Sheet1 (similar to whats below) to summarize the data on Sheet 2 which has data over more than one line. The formula i am using is
=IFERROR(INDEX(Sheet2!$B$2:$D$400,MATCH(1,(Sheet2!$B$2:$B$400=Sheet1!$C$5)*(Sheet2!$C$2:$C$400=Sheet1!B6),0),3),0)
Category | English | French | Spanish |
Complete pack | 39 | 0 | 2 |
Complete pack (excl USB) | |||
Assembled complete pack | |||
Assembled complete pack (exl USB) | |||
Folders | |||
A3 Placemats | |||
Invitation to grow with us | |||
Support for member firms | |||
USB |
<tbody>
</tbody>
Sheet 2 looks similar to this:
Date | Language | Type | Number |
23/10/2014 | English | Complete pack | 39 |
23/10/2014 | Spanish | Complete pack | 2 |
23/10/2014 | English | Complete pack | 200 |
23/10/2014 | English | Complete pack | -20 |
<tbody>
</tbody>
My problem however is that the formula is just picking up the first number (39 for English Complete pack). I would like the formula to Sum 39+200-20 for English Complete pack.
Please let me know how i can fix this.
Thanks (i would upload the excel if i could figure out from where :S)