I hope you can help me with an excel formula I'm having trouble with. I have searched for an answer on google and youtube but can't seem to find one.
In excel I'm using an aggregate function with two criteria. Based on "Location" and "Function" I'm able to extract records from endless rows and columns of data.
I have a dropdown list for both Location and Function. For "Location" I want to be able to choose between: No Selection, Amsterdam, Paris, London and Madrid. For "Function" I want to be able to choose between: No Selection, Banker, Banker II and Assistant.
I'm using the following formula: =IF(ROWS(M$2:M2)>$I$6;"";INDEX(source[Name];AGGREGATE(15;6;(ROW(source[Name])-ROW(source[#Headers]))/((source[Location]=$I$2)*(source[Function]=$I$3));ROWS(M$2:M2))))
I want to be able to extract data based on two criteria but also based on one criteria. That's why I'm using "No Selection". No whenever I choose "No Selection" for Location, I want to be able to extract the data for all the bankers in all of the locations.
The problem I'm having is with the above formula I have to choose two criteria. Whenever I select "No Selection" on one of the dropdown lists it wont extract data.
I know an IF function can be used but I can't get it right. If someone could help me it would mean alot. It's a schoolproject I'm working on and my knowledge about excel is very limited.
Link to an example I'm working with: https://www.dropbox.com/s/mhsfzv7d63y7rb7/example%281%29.xlsx?dl=0
Aucun commentaire:
Enregistrer un commentaire