• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in Formula

M

max4asd

Member
Hi Everyone,


I am working on a Dash board and have created 4 different list values and i want to display the data in the table below to show the numbers based on my selection criteria in each list.


Right now i am using IF condition with SUMIF function but its working fine with me for only 2 list values if i wnat to filter the info based on the other list values what should be the formula.


The below formula is able to filter only 2 list values if i want to add 2 more filter how can i do it.


I appriciate any help provided and thanks in advance.


Max


=IF(J3="All",SUMIFS(Sheet1!$H$3:$H$758,Sheet1!$A$3:$A$758,'Dash Board1'!$C$3,Sheet1!$E$3:$E$758,'Dash Board1'!$G$3,Sheet1!$D$3:$D$758,'Dash Board1'!C$5,Sheet1!$F$3:$F$758,$J$3,Sheet1!$F$3:$F$758,$J$3),SUMIFS(Sheet1!$H$3:$H$758,Sheet1!$A$3:$A$758,'Dash Board1'!$C$3,Sheet1!$E$3:$E$758,'Dash Board1'!$G$3,Sheet1!$D$3:$D$758,'Dash Board1'!C$5,Sheet1!$G$3:$G$758,$J$3))
 
What exactly do you mean by list values? Your SUMIFS functions appear to be handling 5 criterias correctly currently. Are you wanting to add additional criteria? Do you need other options depending on what J3 is?


One small note, did you notice that you repeat a criteria in the first SUMIF?

...,Sheet1!$F$3:$F$758,$J$3,Sheet1!$F$3:$F$758,$J$3)
 
Thanks Luke,


Yes, your right thanks for highlighting it i removed it from the formula but i wnat to attache the sample sheet in order to give you the idea what i nactually needed how can i attach the sheet.


Thanks

Max
 
http://www.2shared.com/file/D3AqE9wx/Book1.html


I have uploaded this file through www.2shared.com

you can use the same
 
See sticky for other uploading options:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thanks Guys,


PLease see the sample file link below.


http://dl.dropbox.com/u/79363919/Test%20Dash%20Board%202012.xlsx


Point1. Now i want to select region # 3 and the branch selection is "All" i want to display total value for all the branches in this region based on the class selected or for the total class if the selection in class is "All".


Point2. If i select region # 3 and select one of the branch "1110" then i should be able to display only the sales for that branch based on the class selected or for the total class if the selection in class is "All"


Could you please suggest me the right formula or is there a better way to display the values in the table.


Thanks

MAX
 
Might it be better to use a PivotTable? Since you've got multiple dropdown filters needed, that might be the easiest way to display all the data. Alternatively, maybe having the PT somewhere else, and using the GETPIVOTDATA function.
 
Thanks Luke,


Thanks for your suggestion but if i want to use the formula that i am currently using what will be the formula for this to achive the desired result.


Thanks

Max
 
Max


How about setting up an Advanced Filter next to the Data Table, to do an intermediate filter based on the values in C1:C3 & J3

Leave the value blank for All

Then use a Sumproduct to extract to the 20111/12 and Months


I would also get rid of all the excess spaces in the Class1 field
 
Thanks Hui,


I didn't the idea I would appriciate if you could do this in the sample sheet i have attached.


Thanks in advance.


Thanks

Max
 
Hi Max ,


I have one suggestion to make :


Since SUMIF / SUMIFS will accept wildcards , instead of directly using the drop-down cells in your SUMIF , have a helper cell with the formula :


=IF(J3="All","*",J3)


Have similar helper cells for the other drop-downs where a choice of "All" is available.


Now use the helper cells in your SUMIF / SUMIFS function.


For example , in C6 , enter the following formula , as an array formula ( using CTRL SHIFT ENTER ) :


=SUMIFS(Data!$H$3:$H$758,Data!$B$3:$B$758,'Dash Board1'!$C$1,Data!$C$3:$C$758,'Dash Board1'!$C$2,Data!$A$3:$A$758,'Dash Board1'!$C$3,Data!$E$3:$E$758,'Dash Board1'!$G$3,Data!$G$3:$G$758,'Dash Board1'!$P$3,Data!$D$3:$D$758,'Dash Board1'!C$5)


where 'Dash Board1'!$P$3 contains the following formula :


=IF(J3="All","*",J3)


Narayan
 
Hi Narayan,


Thanks for your advice but when i try the formula its not working with the other two drop downs in the dash board.


Could you please please check the attached sample sheet and propose the full solution. PLease anybody know the solution could you please propose the full formula.


Thanks
 
Hi Max ,


I was just checking , and saw that the other drop-downs are numeric ; only your Class drop-down is text.


For the other two drop-downs , a different technique will have to be used ; let me think it over and get back. I hope it's not urgent.


Narayan
 
Hi Narayan,


Thanks for taking your valuable time and checking it. I amstill trying for it and will wait for your suggestion anyway i need it by comming Monday.


Thanks

Max
 
Hi Max ,


Can you check out the workbook at this link ?


https://docs.google.com/open?id=0B0KMpuzr3MTVQmlMNDI3aUpnalU


I have done it for a few cells ; see if the results are correct.


Narayan
 
Hi Narayan,


Thanks you very much for your help and solution it works perfect but could you please explain how you did it. Its very important for me know how it works because it will help me big time in future.


I tried to sum up returned value its giving me a #NAME? and when i press F2 i see in the formula tab something you have created for all the values used in the formula as test tags what is this and how to create this?


I must say that your are very smart in excel great job.


Thanks

Max
 
Hi Max ,


Sorry for the delay in replying.


You have 4 drop-downs viz. Country , Region , Branch and Class. Of these , Country and Class will have text data , while Region and Branch will contain numeric data.


The COUNTIFS function accepts wild-cards such as “*” , which represents any number of alphanumeric characters ( text data ) , and “?” , which represents a single alphanumeric character ( text data ). By nature , since these wild-cards are enclosed in double quotes , they represent text data. Hence we can use “*” to represent any Country or any Class , but we cannot use it to represent any Region or any Branch , since these are numbers.


The straight-forward way to solve this problem is to convert the numeric data in your Sub-region ( Branch ) and Region columns to text data ; this is done by using the =TEXT function. The syntax for this function is :


TEXT(value, format_text)


where value is the numeric value which is to be converted to text , and format_text is a string expression ( it has to be enclosed in double quotes ).


This has been done in columns L and M , in the tab labeled Data.


Once we have the numeric data converted to text data , all that remains is to convert the numeric value in the drop-down also to text data ; this is done in cells P2 and P3, in the tab labeled “Dash Board1”.


The final formula , within the two ranges C6:N10 and C14:N18 , have to add two more constraints viz. the year ; the first range considers amounts only for year 2011 , while the second considers amounts only for year 2012 , and the months ranging from “Jan” through “Dec”.


Looking at the formula :

[pre]
Code: 
=SUMIFS(Data_Gross_Volume,Data_Country, $C$1,Text_Region,$P$2,Text_Branch,$P$3,Data_Year,$G$3,Data_Class,$P$4,Data_Month,C$5)
[/pre]
I have used named ranges just as a convenience :


Data_Gross_Volume : =Data!$H$3:INDEX(Data!$H:$H,COUNTA(Data!$C:$C)+1)


Data_Country : =Data!$B$3:INDEX(Data!$B:$B,COUNTA(Data!$C:$C)+1)


Data_Year : =Data!$E$3:INDEX(Data!$E:$E,COUNTA(Data!$C:$C)+1)


Data_Class : =Data!$G$3:INDEX(Data!$G:$G,COUNTA(Data!$C:$C)+1)


Data_Month : =Data!$D$3:INDEX(Data!$D:$D,COUNTA(Data!$C:$C)+1)


Text_Region : =Data!$M$3:INDEX(Data!$M:$M,COUNTA(Data!$C:$C)+1)


Text_Branch : =Data!$L$3:INDEX(Data!$L:$L,COUNTA(Data!$C:$C)+1)


The syntax for the =SUMIFS function is :


=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)


where sum_range is the range whose values are to be summed up , here what we are to sum up is the values in column H , in the tab labeled Data ; this is the named range Data_Gross_Volume.


The remaining parameters are in the form of pairs [criteria_range, criteria] , each criteria_range followed by the criteria. In your case , the criteria_ranges are in the named ranges listed above ; correspondingly , the criteria are in the cells $C$1 , $G$3 , $P$4 , C$5 , $P$2 and $P$3.


$P$2 , $P$3 and $P$4 are the three helper cells , which are used to convert the numeric values in the drop-downs to the text format , and the text value of “All” to the wild-card character “*”.


You will have to remember to modify the criteria when you copy this formula to the table for year 2012 ; the year criterion cell will have to be $H$3 instead of $G$3 ; the month criterion cell can be changed to C$13 , or you can let it remain C$5.


The named ranges are created by clicking on the FORMULAS main menu item , clicking on NAME MANAGER , clicking on NEW , entering the name of the range , and then entering the required formula in the REFERS TO input box.


Narayan
 
You must log in or register to reply here.
Back
Top