Is there a way to easily sum a few columns together, but only if the text next to them matches a dropdown selection

Question

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. https://docs.google.com/spreadsheets/d/1eZ7_yOrkoy_PCgcn_YxscPnDCvLXWK48JW-S7DqEgdQ/edit?usp=sharing

Answer 1

Try the following in C2

=QUERY({{B5:C;D5:E;F5:G},{J5:J;J5:J;J5:J}},
         "select sum(Col1) where Col2='"&A2&"' and Col3='Left'
          label sum(Col1) '' ",0)

For cell D2 all you need to do is use Col3='Right'

=QUERY({{B5:C;D5:E;F5:G},{J5:J;J5:J;J5:J}},
         "select sum(Col1) where Col2='"&A2&"' and Col3='Right'
          label sum(Col1) '' ",0)

In case you want to add more ranges like columns H-I you would adjust your formula like:

{{B5:C;D5:E;F5:G;H5:I},{J5:J;J5:J;J5:J;J5:J}}

(Do adjust the formula according to your ranges and locale)