Search code examples
arraysfiltersumgoogle-sheets-formulasumifs

How to integrate a month criteria in filter function in Google Sheets?

Table 1:

Table 1

Table 2:

Table 2

This was my original query that's in Table 2, taken from Table 1.

=SUMIFS(C:C,D:D,"Necessities Others",B:B,"Visa",A:A,">="& DATE(Year(today()),1,1),A:A,"<="& EOMONTH(DATE(year(today()),1,1),0))

It would add everything in the column Payment (C:C) that had the Necessities category (D:D) that uses the Visa account (B:B), in a certain month (A:A) -- January in the example above.

But I switched to using the filter function because I wanted multiple criteria for Necessities

=SUM(FILTER(C:C,REGEXMATCH(D:D,"Health/Medicine|Education|Necessities Others"),B:B="Visa",...

but I don't know how to incorporate my previous query on month to this format. Help?

Solution

  • try:

    =SUM(FILTER(C:C, 
 REGEXMATCH(D:D, "Health/Medicine|Education|Necessities Others"), 
 B:B="Visa",
 A:A>=DATE(YEAR(TODAY()), 1, 1),
 A:A<=EOMONTH(DATE(YEAR(TODAY()), 1, 1), 0)))