Google Sheets – Sumif and Filter Using Regexmatch with Multiple Criteria

filterformulasgoogle sheetsgoogle-sheets-queryregex

I need to sum specific cells in 1 column (Q) based on if the other columns (R, S) have a certain word(s) within the cell.

For example in the picture if a cell in R or S contains the word toy or green, then it needs to sum the specified data from column Q.

I have tried using different variations of sumif-filter-regexmatch but I still can not get it to work with multiple conditions. If there is another set of formulas to use please show me.

Thank you!

P.S (I need the sum from the Q column not the count of cells)

Best Answer

You can use the following query formula:

=QUERY(Q2:S11, "select sum(Q) where R matches '(.*green.*)|(.*toy.*)' 
                                 OR S matches '(.*green.*)|(.*toy.*)' label sum(Q)''")

About QUERY

example for those who need to use `FILTER` formulas and struggle with range mismatch of `FILTER`

=FILTER(A1:A, MATCH(A1:A, B1:B, 0))

=FILTER(A1:A, COUNTIF(B1:B, A1:A))

=FILTER(A1:A, ISNA(MATCH(A1:A, B1:B, 0)))

=FILTER(A1:A, NOT(COUNTIF(B1:B, A1:A)))

in case you need to use FILTER formula to return evaluation between two ranges, and those two ranges are of different size (like when they are returned from a query) and can't be altered to match the same size and you just got FILTER has mismatched range sizes. Expected row count: etc. error, then this is a workaround:

to keep it simple let's say your ranges for the filter are A1:A10 and B1:B8, you can use array brackets {} to append two virtual rows on range B1:B8 to match size A1:A10 by using REPTwhere number of needed repetitions shall be calculated by a simple calculation between initial ranges.

then to this REPT formula, we need to add +1 as a correction/failsafe (in a case the difference between two initial ranges is 1), because REPT works with a minimum of 2 repetitions. so in a sense, we will need to create a range of B1:B11 (from B1:B8) and laters we will just trim off the last row from a range so it would be B1:B10 against A1:A10. we will use 2 unique symbols for REPT

next step would be to wrap REPT into SPLIT and divide by 2nd unique symbol. then, (based on a further need) this SPLIT needs to be wrapped into TRANSPOSE (because we want to match column size to column size) and the last step would be to wrap it into QUERY and limit out the output again by simple math of COUNTA(A1:A10) to trim off the last rept cell. put together it would look like this:

=FILTER(A1:A10, NOT(COUNTIF(QUERY({B1:B8; 
 TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(A1:A10)-COUNTA(B1:B8)+1), "♀"))}, 
 "limit "&COUNTA(A1:A10), 0), A1:A10)))

Best Answer

Related Solutions

Google Sheets – SUM(FILTER) Syntax with Multiple Conditions

Google-sheets – FILTER has mismatched range size; referencing the second last cell in a column in range

example for those who need to use FILTER formulas and struggle with range mismatch of FILTER

Related Topic

example for those who need to use `FILTER` formulas and struggle with range mismatch of `FILTER`