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

CodePudding user response：

=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.