SUMIFS with Variable Criteria
Hello all. I am having trouble find an existing answer to this in the community so I thought I would post my question. Forgive me if this has been covered.
Scenario: I have 1 data sheet that is populated with many rows. Going left to right, each row has six descriptive fields. Some are single-select dropdowns and some are text. Then the rightmost field following the six descriptive fields is a number.
I have a second sheet, I'll call this the query sheet, with the same six descriptive fields followed by the same number field.
Based on what values are placed in the six descriptive fields of the query sheet, I am trying to write a formula in the number field of the query sheet that will use whatever values have been placed in six descriptive fields to identify and sum (SUMIFS) all matching rows in the data sheet (essentially a query).
I have been able to make this work but with 1 major shortcoming that I have been unable to solve. In the query sheet, I do not want to require that ALL of the 6 descriptive fields be required in order to perform the match query successfully. For example, in the query sheet, I want to be able to provide only descriptive field 3 and have have the formula sum all data that matches field 3 only. Another example is if I provide fields 2 and 6, I want the SUMIFS to sum all fields matching only fields 2 & 6. If fields 1, 2, 4 & 5 are populated in the query sheet, the SUMIFS results should be based on those 4 fields only...and so on.
I have been able to get this to work with field 1, field 1& 2, field 1, 2, & 3, etc. but my logic breaks if I skip field 1 or if I skip any field thereafter after as I add fields. Presently, I do not have to use all 6 descriptive fields for the match/sum to work but I have to use contiguous fields beginining with field 1 - field 1 only or field 1 & 2 or 1, 2 & 3, etc.
Any thoughts on how to solve this problem would be greatly appreciated. Thanks in advance for your help!
Help Article Resources
Check out the Formula Handbook template!