SUMIFS with Variable Criteria

Michael.M

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!

Michael.M

Leibel, thank you! I am not sure fully understand the formula you provided just yet BUT initial testing appears VERY promising. Thanks again!!!

Paul Newcome

Are you able to provide some screenshots for reference?

Michael.M

Sure Paul. The Data Sheet looks something like this. I really appreciate any advice you can offer.

Michael.M

Again, the "query" sheet format is identical to the Data sheet with the exception of the Assigned Alloc field that calculates the sum of all records matching whatever criteria (full or partial) provided in the first 6 field. I would like to add that I foresee the need to wrap this calculation, once working, in an IF statement so that it only performs the calculation when 1 or more fields are populated.

Paul Newcome

Ok. On the sheet where your formula is going... Is each person listed multiple times or only once? Can you provide a screenshot of that as well? Manually input the expected result and describe in detail the logic behind each one if you are able.

Leibel S

@Michael.M

Below is an example of a formula I use for these situations (you would need to change slightly to match your process). The key is how the criteria is being written.

The criteria returns true if either the filter column (eg Role@row) is blank, or it returns true if the filter column has the '@cell'

=SUMIFS(Sum:Sum, Category:Category, OR(ISBLANK([Category Filter]@row), HAS([Category Filter]@row, @cell)), Role:Role, OR(ISBLANK([Role Filter]@row), HAS([Role Filter]@row, @cell)))

Leibel S

@Michael.M

Another bonus idea, if the Assigned To Column could have multiple people, you can make multiple Assigned To filter columns in your query sheet. This would allow you to filter based on different contacts separately and combined.

so If lets say I want to sum where Person 1, or Person 7, Or Person 22 is in the Assigned to column i would add each of them separately in my Assigned To Filter Columns. As well, if i wanted to see only those where either Person 1 and 2 were both assigned, or where Person 6 is assigned, Or where Person 12 and 13 are both assigned, i would add that info each into my 3 columns (you can technically make as many as you need).

=SUMIFS(Sum:Sum, Category:Category, OR(ISBLANK([Category Filter]@row), HAS([Category Filter]@row, @cell)), Role:Role, OR(ISBLANK([Role Filter]@row), HAS([Role Filter]@row, @cell)), [Assigned To]:[Assigned To], OR(COUNT([Assigned Filter 1]@row:[Assigned Filter 3]@row) = 0, HAS(@cell, [Assigned Filter 1]@row), HAS(@cell, [Assigned Filter 2]@row), HAS(@cell, [Assigned Filter 3]@row)))

Michael.M

Paul, in the sheet with the formula, what I am calling the 'query' sheet, it is possible for the person to be listed multiple times (any field could be listed multiple time), it just depends on what type of data is being requested in each row.

Sample of the 'query' sheet and explanations are below:

Michael.M

Leibel, thank you! I am not sure fully understand the formula you provided just yet BUT initial testing appears VERY promising. Thanks again!!!

Leibel S

@Michael.M

If you need help completing feel free to reach out