Hello Everyone,
I have once came to this forum with a complicated case and had great results, so Im Back.
I have a tremendous Raw Data, and I want to be summarize it in a front page as so:
<tbody>
</tbody>
The first set are drop down boxes which govern the Data shown on the Box Below, so far I have managed to set the equations to return the variations in: COUNTRY, SI and PRODUCT FAMILY, with the following equation: =IF($C$5="None",SUMPRODUCT((ISNUMBER(MATCH('2013 Q1'.$T$2:$T$2000,$C$6,0))),'2013 Q1'.$B$2:$B$2000),(IF($C$6="None",SUMPRODUCT((ISNUMBER(MATCH('2013 Q1'.$C$2:$C$2000,$C$5,0))),'2013 Q1'.$B$2:$B$2000),SUMPRODUCT((ISNUMBER(MATCH('2013 Q1'.$C$2:$C$2000,$C$5,0)*MATCH('2013 Q1'.$T$2:$T$2000,$C$6,0))),'2013 Q1'.$B$2:$B$2000)))) for the Currency value, and for the Quantities I am using =IF($C$6="None",SUMPRODUCT((Sheet20.$AC$2:$AC$5000=$C$5)*(Sheet20.$K$2:$K$5000=U1)*1),(IF($C$5="None",SUMPRODUCT((Sheet20.$K$2:$K$5000=U1)*(Sheet20.$AE$2:$AE$5000=$C$6)*1),SUMPRODUCT((Sheet20.$AC$2:$AC$5000=$C$5)*(Sheet20.$K$2:$K$5000=U1)*(Sheet20.$AE$2:$AE$5000=$C$6)*1)))).
Now I will have 63 Permutations in total, is there an easier to do this which I am not seeing? I am also afraid that using the total of 63 permutations will crash the program.
Thanks for taking the time to read and I /bow to the one that can help
Ord
I have once came to this forum with a complicated case and had great results, so Im Back.
I have a tremendous Raw Data, and I want to be summarize it in a front page as so:
<style type="text/css"><!-- body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small } --> </style>
<colgroup width="132"></colgroup> <colgroup width="186"></colgroup> <colgroup width="137"></colgroup> <colgroup width="106"></colgroup> <colgroup width="203"></colgroup> <colgroup width="113"></colgroup> <colgroup width="132"></colgroup> <colgroup width="181"></colgroup> <colgroup span="2" width="132"></colgroup> <tbody> </tbody> |
<tbody>
</tbody>
The first set are drop down boxes which govern the Data shown on the Box Below, so far I have managed to set the equations to return the variations in: COUNTRY, SI and PRODUCT FAMILY, with the following equation: =IF($C$5="None",SUMPRODUCT((ISNUMBER(MATCH('2013 Q1'.$T$2:$T$2000,$C$6,0))),'2013 Q1'.$B$2:$B$2000),(IF($C$6="None",SUMPRODUCT((ISNUMBER(MATCH('2013 Q1'.$C$2:$C$2000,$C$5,0))),'2013 Q1'.$B$2:$B$2000),SUMPRODUCT((ISNUMBER(MATCH('2013 Q1'.$C$2:$C$2000,$C$5,0)*MATCH('2013 Q1'.$T$2:$T$2000,$C$6,0))),'2013 Q1'.$B$2:$B$2000)))) for the Currency value, and for the Quantities I am using =IF($C$6="None",SUMPRODUCT((Sheet20.$AC$2:$AC$5000=$C$5)*(Sheet20.$K$2:$K$5000=U1)*1),(IF($C$5="None",SUMPRODUCT((Sheet20.$K$2:$K$5000=U1)*(Sheet20.$AE$2:$AE$5000=$C$6)*1),SUMPRODUCT((Sheet20.$AC$2:$AC$5000=$C$5)*(Sheet20.$K$2:$K$5000=U1)*(Sheet20.$AE$2:$AE$5000=$C$6)*1)))).
Now I will have 63 Permutations in total, is there an easier to do this which I am not seeing? I am also afraid that using the total of 63 permutations will crash the program.
Thanks for taking the time to read and I /bow to the one that can help
Ord