Sumifs/Index/Match or Sumifs criterial range 1 multiple columns

[kalyan46]

I am trying to figure out how to use Sumifs/Index/Match or Sumifs (using criteria range 1(multiple columns).
I can get it to work referencing only 1 column in the criteria selection, however I need the logic to change based on a validation list drop down (Cell Z1) to choose the correct column (Q-U).

Please see attachment as reference.

Thank you for your time

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also will the value in Z1 be the same as the column header, or the text in the columns?

 

[kalyan46]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also will the value in Z1 be the same as the column header, or the text in the columns?

Yes the column headers will match the drop down list

 

[kalyan46]

Yes the column headers will match the drop down list

Microsoft 365

 

[Fluff]

Thanks for that, so you want to sum col I if the date matches & there is anything in the column selected by Z1?

 

[kalyan46]

Thanks for that, so you want to sum col I if the date matches & there is anything in the column selected by Z1?

yes, sum column I, based on Date and selection criteria in Z1. Z1 results will vary columns Q-U
Thank you for your time

 

[kalyan46]

yes, sum column I, based on Date and selection criteria in Z1. Z1 results will vary columns Q-U
Thank you for your time

Z1 is just a dropdown list based on Q1-U1

 

[Fluff]

Yes but if col Q is selected, do you want to sum col I based on col Q having any value on the relevant rows?

 

[kalyan46]

Yes but if col Q is selected, do you want to sum col I based on col Q having any value on the relevant rows?

Correct,
example if secondary is selected in Z1, I would like Column S selected along with dates and Qty (col I)

 

[Fluff]

Ok, how about

=SUMIFS(I:I,B:B,Y4,INDEX(Q:U,,MATCH(Z1,Q1:U1,0)),"<>")