# Need help simplifying an Equation with 63 variants

#### Ordonius

##### New Member
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:

<style type="text/css"><!-- body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small } --> </style>
 SubRegion N-Africa Country United Kingdom SI None Product Family None Family Segment Addon Renewal YES 3Year Deal YES Country United Kingdom FY13 Qty of PO FY13 % of Total United Kingdom FY14 Qty of PO FY14 % of Total United Kingdom FY15 Qty of PO FY15 % of Total Q1 1,551,446 € 99 31.11% 3,243,153 € 116 47.45% 4,495,647 € 123 52.89% Q2 2,373,125 € 120 51.58% 1,301,569 € 112 26.08% 1,375,537 € 140 21.15% Q3 1,819,876 € 128 46.18% 3,265,357 € 130 48.68% Q4 3,270,126 € 103 48.58% 4,139,968 € 86 51.77% Total 9,014,573 € 450 44.49% 11,950,047 € 444 45.04% 5,871,184 € 263 39.14%

<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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Replies
68
Views
2K
Replies
4
Views
316
Replies
1
Views
3K
Replies
4
Views
754
Replies
1
Views
446

1,191,550
Messages
5,987,225
Members
440,085
Latest member
MBecker79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back