Search code examples
regexgoogle-sheetsfiltersumgoogle-sheets-formula

Sum variable number of columns with multiple criteria on multiple columns

Looking for some help on creating a formula (ideally Google Sheets compatible) that will allow me to define criteria range for multiple columns and define how many columns to sum.

For example, in the screenshot provided, I am interested in:

  • Names A, B, and C
  • Program X and Z
  • Sum of the first two months (Jan and Feb)
  • I would expect the result to give 1050, and if I change the Months criteria to 3, it would add in March as well and total to 550

Example

Thanks!

Solution

  • use:

    =SUM(FILTER(FILTER(C2:F10, COLUMN(C1:F1)-COLUMN(C1)<C12), 
 REGEXMATCH(A2:A10, TEXTJOIN("|", 1, A12:A15)), 
 REGEXMATCH(B2:B10, TEXTJOIN("|", 1, B12:B15))))

    enter image description here