There are 2 sheets: Data and Stats. Data contains records (several hundred) that are divided into ~20 categories. On the Stats sheet, I want to do various calculations. Is there a way to compose formulas in a way to take in only the categories that are enabled?

Previously, this was implemented in Excel, where SUMIFS function makes it possible (the conditions are inclusive). But in GS SUMIFS does not evaluate further conditions if condition1 is true. Or: I'm doing something wrong. Totally possible.

=SUMIF(Data!K:K, "<="&G3, Data!N:N) This version is a single condition. It says 'give me the sum of N column on Data sheet, provided corresponding value of K is less than or equal to G3'.

SUMIFS(Data!N:N, Data!K:K, "<="&G3, Data!B:B, G2:G20) (the syntax is slightly different in comparison with SUMIF)

Here, range G2:G20 contains the list of categories that I want to be able to switch on and off, and B column contains category labels. In Excel, when you indicate a range like this, it works. In GS - not so much.

Regardless of the Excel implementation, what would be the best way to achieve this on/off functionality in Google Sheets?

CodePudding user response：

try:

=SUMPRODUCT(FILTER(Data!N:N, Data!K:K<=G3, 
 REGEXMATCH(""&Data!:B:B, TEXTJOIN("|", 1, G2:G20))))

note that G3 is overlapping with G2:G20 so something is not right in your referencing tho