I've been trying to figure out what i'm doing wrong here when i'm doing the sumif formula's in b2,c2,d2 I have a lot going on, I realize. The data we are looking at, is between L5:U21

I have a query in a5 that pulls from l5:U that pairs any data in n5:n,p5:p,r5:r,t5:t to the selected data in the dropdown in a2. This part is working correctly for what I need.

B2 I am trying to extract from the top 3 options in the range b5:J that match a2, and add them together. Ultimately I'd like to do this if they do not have "Left" or "Right" in the J column as well. To achieve this I pulled the data from b5:I into a sortn function seen in y5.

=SORTN(B5:I,3,,B5:B,false,D5:D,false,F5:F,false,H5:H,false)

and then my SUMIF function is as follows: =SUMIF(Z5:AF,A2,Y5:AE)

C2 is similar to B2, but I only data that matches the selection in a2, but also have "Left" in the J column.

I tried to achieve this with a similar SUMIF function i'm using in b2, but it seems to only pull the left most cell's data in the range given, not the matching column's data. So lets say if e9 = example1, it doesn't then grab the matching 2 in d9, it grabs whatever is in b9 only, and adds that. Which right now, it adds them all. I want to ultimately only pull the top 1, but I cannot even get it working correctly with all of them. =SUMIF(J5:J,"Left",B5:H)

D2 is the same as C2, but "Right" in the J column.

This is my example / testing document I created to get a closer look at what's going on, if what i'm explaining isn't making a ton of sense.

CodePudding user response：

SUGGESTION

I was experimenting earlier with SUMIFS & QUERY formulas to no success, as I also have a limited knowledge when it comes to implementing advanced Google Sheet formulas in a complex scenario. What I can suggest you try is by using a Custom Function formula in Google Sheet made possible by Google Apps Script that's integrated to the Google Sheet service.

This custom formula function will filter the range that does not contain Left or Right in column J based on the selected drop-down data, and then it returns the top 3 results in descending order.

The Custom formula Script named as CUSTOM_FUNC

/**
 * Filters data in descending order from a range that doesn't contain any Left & Right based on selection in a cell dropdown selection & return the sum of the top 3 result.
 *
 * @param {B5:J21,A2}  reference  The range to be used.
 * @returns                       The range and the cell reference to used in filtereing the data.
 * @customfunction
 */
function CUSTOM_FUNC(data,dropdown_selection) {
  /**Filter data that do not contain Left or Right on column J */
  var lvl1 = data.map(x => {return x.toString().includes('Left') || x.toString().includes('Right') ? null : x }).filter(y => y)
  /**Further filter lvl1 that matches the drop down selection*/
  var res = lvl1.map(d => {
    return d.map((find, index) => {return find == dropdown_selection ? d[index-1] : null}).filter(z => z)
  });
  /**Return the top 3 result in descending order */
  return res.sort().reverse().slice(0, 3);
}

The parameters of this customer formula would be:

=CUSTOM_FUNC(data,dropdown_selection)

• data

The sheet range (e.g. B5:J21) where the data you'd like to be processed resides.

• dropdown_selection

The cell reference of the drop-down selection on your spreadsheet file.

To add this script in your spreadsheet file, copy and paste the script as a bound script in your Spreadsheet file by following