Sum variable number of columns with multiple criteria on multiple columns

user_18154214 0
Viewed 116

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!

google-sheets-formula google-sheets filter sum regex
2 Answers

=SUM(FILTER(INDEX(C2:F5,SEQUENCE(ROWS(C2:F5)),SEQUENCE(1,C12)),(COUNTIF(A12:A14,A2:A5))*(COUNTIF(B12:B13,B2:B5))))

Requires Office Excel 365. The filter is set over the number of columns as mentioned in C12 and it filters the rows that meet the COUNTIF conditions.

If you have an older version of Excel I could workaround the SEQUENCE part using double INDEX.

enter image description here

user_126342300

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

user_56326290
Related