Combo box with All option

[Irshath]

I have two combo boxes year and Location.. based on that Im using Sumifs() funtion to calculate the Total Sales. But now I need a option in the Location Combo box 'All', if I select All it should calculate all the Total based on the selected year only.


(Like Pivot Table's All option)


For e.g. Year -2011, Location - All, Total - 5000,

Year -2011, Location - India, Total - 2000.

Year -2011, Location - USA, Total - 1000

Year -2011, Location - UK, Total - 2000

 

[Hui]

Irshath


The "All" is often replaceable by not including that parameter in formulas


for example


=if(A1="All", Sumproduct((Year=A2)*Sales), Sumproduct((Year=A2)*(Country=A3)*Sales) )


Note that All Countries are included by excluding them in the first Sumproduct, rather than try and write a complex matching formula

 

[Irshath]

Thanks a lot Hui!, even I was thinking in the same simple method.. but tried here for some other esay method.. if possible.. .. because Ive used loads of formula in my file for multiple dashboards.. The file size is 35 MB now.. then I saved it in the Excel Binary format.. now its reduced to 15 MB.


So I thought increasing the Formula size will increase the file size again.. ..


Im sorry to explain all this problems... to you.. Now Im thinking to use Macros to copy paste the formulas and pastespecial values..in the File open event and Combo box selection event... to reduce the file size without affecting the Dashboard function...


Please let me know, if any other options to reduce the file size, also using multiple formulas at the same time. I have 13 Dashboards and 25 helping sheets in one file. All the sheets have 1000s of formulas. More than 70,000 sumif/Sumifs. . If I use Sumproduct instead of Sumif/Sumifs this will help me to reduce the file size?