LinkBack URL
About LinkBacks
I am trying to get a text value with a sumifs function, is this possible or should I use another function?
See attached file!
Thank you,
Waimea
I am trying to get a text value with a sumifs function, is this possible or should I use another function?
See attached file!
Thank you,
Waimea
="Here they are building "&INDEX(F4:F7,MATCH(1,INDEX((E4:E7=$B$4)*(H4:H7="Yes"),),))&" seats"
Thank you, that gives me the total of seats! Is it possible to get the names of the company (A,B,C,D) in the result?
Try:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you JeteMc! It works great!
Could you explain the different parts of the formula? (So that I can learn)
I understand iferror and index.
Since you understand IFERROR and INDEX I'll just explain AGGREGATE.
The AGGREGATE function as used in the formula I posted would replace array entered SMALL(IF based formulas that were common syntax in the versions prior to 2010. The argument 15 is SMALL, used to find the Kth smallest row that meets the criteria of a City being the Selected City. The argument 6 ignores errors that that may occur when dividing the row numbers by FALSE. The first use of the ROW function, ROW($5:$8)-4, produces an array of row numbers 1, 2, 3, 4 and the second use of the ROW function, ROW(1:1) is a counter for the argument K. I hope that I covered that well enough, let me know if not.
I suggest that you select cell E12 and use the Evaluate Formula feature (Formula tab in the 2010 version) to see how the functions work together.
Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.
I am reposting in this thread instead of creating a new thread and I hope it is ok to revive old threads.
I want to extract a lot of information from my data table to my formula, at the moment I am using sumifs.
I would like to see what company is building in what muncipality, what year they are building it and how many places they are building!
See attached workbook for more details!
All help is very much appreciated.
In general it is best to start a new thread when you have a new question as old threads often are only monitored by the original participants.
In this case I may be able to help. I believe that you want a formula similar to:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Hi JeteMc,
thank you for your reply and your code! This works great, now I have yet to understand the code!
I have one question, does this code take the "In production" column into account? Where only buildings with "Yes" for "In production" are taken into account?
Could you help me to update the formulas to use the "In Production" column into account? Where only "Yes" cases are showed.
For the company column the formula would read:Formula:Please Login or Register to view this content.
The others would be similar although the indexed columns would be F for seats and J for build year.
Let us know if you have any questions.
Hi JeteMc,
thank you for updating your code! I am going to try it at once!
Good morning,
I am looking at your formula.
I understand thepart.
Please Login or Register to view this content.
I understand the ranges forandPlease Login or Register to view this content..Please Login or Register to view this content.
I don't understand the use of row()-4 and Row(1:1) ???
Please Login or Register to view this content.
I can't get it to work, I have my data table in another worksheet but I can't get the ROW(Worksheetname!$5:$14) to work? Same goes for ROW(Worksheetname!1:1).
I got it to work! Your formula is really clever!
Is it possible to calculate in what year a company should build an arena??
The arguments for ROW will work without the worksheet reference -- i.e. ROW($5:$14) and ROW(1:1)
ROW($5:$14) sets up an array of numbers {5, 6, 7 ... 14}
The -4 changes that array to {1, 2, 3 ... 10}
ROW(1:1) is the [k] argument for AGGREGATE, it will change as the formula is dragged down the column so that on the first row ROW(1:1) evaluates to 1 and on the second row ROW(2:2) evaluates to 2.
Looking at the file attached to post #19, selecting cell J20 and running the Evaluate Formula feature may help in understanding how the formula works.
Let us know if you have any questions.
OOPS I responded to post #15 and didn't see that the posts on the second page of this thread had been posted. Glad that you got the formula to work.
Last edited by JeteMc; 12-13-2018 at 10:03 AM.
I got it to work and it is a really smart formula, thank you for your explanation which helped me now to understand why -4 is deducted.
To include a Build Year restriction, put the desired year in a cell (say M19) and then amend the formula to read:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Thank you for your reply!
I have put a year in cell M19, in what cell should I put the new formula?
Put the formula in J19 then drag the fill handle down. You'll also want to drag the formula to the right to populate the Seats and BuildYear columns. Be sure to change the INDEX references accordingly (i.e. INDEX(F$5:F$14... for the Seats column and INDEX(J$5:J$14... for the BuildYear column).
Let us know if you have any questions.
Thank you for your reply, I like your code and have changed the index to show me more data from my data table.
I see what you did with the building restriction but I am trying to figure out in what year/years there are gaps.
A gap could be in 2024 when no company is building, I also have the need for seats for each city.
Is it possible to calculate in what year I should build based on what the competition is doing??
Perhaps store the build years in an array and manipulate the array in some way?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks