Search results

Thank you again. That second formula worked fine. I was able to combine the two formula steps into one formula and it worked. I appreciate the explanation as well. Learning about setting the row or column within INDEX to zero or blank causes the function to return an array instead of a single...

Hi pecoflyer, Thank you for your suggested approach. The first formula in J168 using AGGREGATE is very interesting and works spot on. I tried using the evaluate formula tool to understand the function but it kind of skips steps. If you can explain how that is works that would be great. Your...

Looking for formula that can do the lookup shown in attached table with the added criteria that the lookup value not include products that have 100% VOC content. In other words incorporate criteria (e6:e13<1) at the appropriate place in the formula at J160. So in my real sheet, I have a drop...

I agree and I think I follow what your saying. Could you please show me with the sample provided so I may get a clearer understanding. I forgot to mention that I am using Excel 2019.

Peter, I see what you did, you made a true array for Div Counter using named ranges. Very good technique. I can sort by any column on Master and it does not affect the other two Dividend sheets. However, what I need is if I sort Master by Security Type (Z-A) to "group" together let's say all...

vletm: I did not realize that Excel is limited on what you can do with the data when rows have both fixed and formula based values (ie., sort). Your observation of the change in CC’s received dividend becomes a different stocks value is true. I would like to make modifications to get this to...

I have developed a portfolio tracking workbook consisting of the following worksheets: Master Dividend Per Share Dividends Received See attached file. Note that the Master holds all of the pertinent investment data. The Dividend Per Share and the Dividends Received worksheets have specific...

Thank you XOR LX as your solution does work albeit a bit more complicated than I imagined. Not sure if it is more efficient than my original formula solution calling SEARCH/MAX three times. Chihiro, very impressive solution but I apologize as your solution is way too complex for me. I have...

Thank you Nebu for your pivot solution. However, I am looking for a formula approach as the actual spreadsheet is setup a bit different and I need the sum in the one cell and no helper columns.

Hi, Looking for shorter formula to sum the highest values for each part description type. This formula does work...

Hi xlstime, Thank you for your assistance as it is a solution that works if I change F46 to just 24. However, it looks like you did use a helper column and the SUMPRODUCT formula is not an array formula (opposite of what you stated). I am wondering if there is a non-helper column solution...

I am in need of a solution to count the number of times the word “Strip” and "Prep" appears in a column but stop when the cumulative time in another column reaches 24 hours (Elapsed Time hrs column). Data is set up like this: Start Stop Mode Elapsed Time (min)...

Using the attached file in the first post. At Worksheet "2015 Summary by Category", for each name in column D, I would like to sum the corresponding total points in column H from all worksheets having month and year (i.e., Jan 2015) for the corresponding name (produced by your Macro above) and...

Thank you Deepak that worked spot on. Would it be possible to extend that same idea to grab the totals in column H of each worksheet for the names by category? Either in a separate code (macro) or in the same code.

I am not good at Macros, but it would be worth a try.

Hi, Could you assist me on finding a solution as no one has come forward. http://chandoo.org/forum/threads/summary-table-from-other-worksheets.22233/ Thanks

I am in need to create a summary table of cumulative points for each participant that has entered in monthly competitions. Currently I have a summary worksheet that I have to manually enter names when new members join and submits to the competition. I am looking for a way to automatically...

Tiago, I appreciate your explanation, and follow some of it but not all of it. I generally understand the concept of array formulas, but fail to understand how the LOOKUP portion is forming an array of matches (as opposed to just finding the first occurrence like INDEX/MATCH would) and how...

Thanks for the SUMPRODUCT vs SUM explanation. Haseeb, thanks for carifying the need for the having the "# Tested" as the first cell in the range. I am still having a bit of trouble understanding what LOOKUP(ROW,ROW/range="# Tested") does however.

Narayank991, that is another clever approach, although it still relies on helper columns. Interesting use of SUM(IF and SUMPRODUCT(IF. I think it does not make any difference in this case as I typed SUMPRODUCT in place of SUM and it worked.

Kyle, your approach seems to work fine. I would prefer not to create helper columns, however, your idea about concatenating the two criteria may spur another approach (if possible) in placing the concatenation within the formula without the helper columns. Haseeb, I tried your formula and I...

Hi, Need assistance in creating a summary table based on two criteria from an array of stacked tables one after another. The tables have been rearranged as suggested earlier (see link to other post -...

Thank you SirJB7, Appreciate the observations and Whole Year macro. I will look to see how you did that later and study the questions.

Thanks Faseeh, That was very helpful. I don't have Excel 2007 or above, so your formulas using IFERROR showed up as #NAME?. I managed to get the formula working without error checking. So for E9: ' = INDEX('2012_All'!$B$2:$B$272,LARGE(IF(('2012_All'!$C$2:$C$272=$B$9)*...

Thanks Haseeb. Unfortunately it is too complex for me to simply adapt to my situtation. Is there any way I can send you the file or tell me how I can get the file to you via PM? Is there a way to have just the month year not be hyperlinked? Also, for the example you provided, why is...