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

 

[kalyan46]

When I tried to use this formula, it is only summing on the date, it is not calculating based on Z1 selection

 

[kalyan46]

When I tried to use this formula, it is only summing on the date, it is not calculating based on Z1 selection

Answer should be 417

 

[Fluff]

That suggest that the 1st 2 rows in col Q are not empty. What is in them?

 

[kalyan46]

That suggest that the 1st 2 rows in col Q are not empty. What is in them?

XLookup showing "" if not found

 

[Fluff]

Can you post the actual formula

 

[kalyan46]

Can you post the actual formula

Vlookup sorry about that
=IF(ISERROR(VLOOKUP($C2,Xref!D:D,1,FALSE)),"",Q$1)
**
C2 refers to a type code which brings back the values in yellow cells

 

[Fluff]

Ok, how about

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

as long as the entries are always text

 

[kalyan46]

Ok, how about

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

as long as the entries are always text

Worked!
Thank you, I really appreciate it
Can you explain the wildcard using the ?* logic?

 

[Fluff]

It's looking for any cell with 1 or more characters, so it ignores the nullstring from the formula, but doesn't work with numbers.