Google Sheets Formula to find specific months

Question

I have a Google Sheets document with multiple sheets.

What I want to do is perform a lookup on a specific sheet Expenses, and find all the expenses for a given month and year.

Here's what I have so far, but I keep getting a #VALUE! error:

=SUMIFS('Expenses'!$E$4:$E,
     'Expenses'!$D$4:$D, ">="&DATE($E$3, $C$3, $D$3),
     'Expenses'!$D$4:$D, "<="&DATE($I$3, $G$3, $H$3),
      MONTH('Expenses'!$D$4:$D), 3)

So, if I put in 1/1/23 and 12/31/23, I'm hoping that it will find all expenses in March 23, add them together, and spit out an answer.

In the sheet Expenses:

• Column E is a list of costs ($1.00, $10.00, $6.75, etc).
• Column D is a list of dates (1 May 2022, 10 Aug 2023, 18 Sep 2024, etc).

Lookup dates in the sheet with my calculation (Month, Day, Year):

• Starting: C3, D3, E3
• Ending: G3, H3, I3

I want the result to be the sum of all the expenses in the given date range by month.

Answer 1

Pointing to the red triangle in the cell holding the formula, a popup details the error.

Screenshot showing error popoup

Error
Array arguments to SUMIFS are of different size

Putting the SUMIFS function inside ARRAYFORMULA() might fix the problem.

=ArrayFormula(SUMIFS(Expenses!$E$4:$E,
     Expenses!$D$4:$D, ">="&DATE($E$3, $C$3, $D$3),
     Expenses!$D$4:$D, "<="&DATE($I$3, $G$3, $H$3),
     MONTH(Expenses!$D$4:$D), 3))

Answer 2

SUMPRODUCT

SUMPRODUCT is a single array function that avoids the need to combine SUMIF with ARRAYFORMULA.

=SUMPRODUCT(
   Expenses!E4:E,
   Expenses!D4:D>=DATE(E3, C3, D3),
   Expenses!D4:$D<=DATE(I3, G3, H3),
   MONTH(Expenses!D4:D)=3)

EOMONTH

1. If you are trying to return all transactions where the date is within a particular month, EOMONTH will allow you to compare two dates while ignoring the days.

   EOMONTH(start_date,‎ months) 
   

2. Unlike the MONTH function, EOMONTH returns a date, not a text string allowing you to effectively compare the month and year simultaneously.

3. For any two dates in the same month and year, EOMONTH will make them equivalent. For example:

   EOMONTH(01/25/2024,)  =  EOMONTH(01/11/2024,)  // Same month and year 
   EOMONTH(01/01/2023,) < > EOMONTH(01/01/2024,)  // Same month, different years 

4. Your existing formula would become:

   =SUMPRODUCT(
      Expenses!E4:E,
      EOMONTH(Expenses!D4:D,)=EOMONTH(DATE(E3, C3, D3))
   

   DATE  EOMONTH  SUMPRODUCT

Multiple Months & Categories

You mentioned in a comment your goal was to be able to get results broken out for multiple months and perhaps also break them down by expense category.

Here is a sample formula based on the above, but extended to return more comprehensive information.

Expenses Sheet

I used the existing ranges from Expenses that I knew about and:

1. I added a Category column in column F.

2. I put a formula in Column J to generate a list of unique Category names for a dropdown. This would normally be hidden away somewhere on another sheet:

   ={"CATEGORY DROPDOWN (DO NOT EDIT)";"*All";
      UNIQUE(SORT(TOCOL(F2:F,1)))}
   

   SORT  TOCOL  UNIQUE

3. The data in Expenses in my example starts in row 2 (yours was in row 4).

4. Basic layout of the sheet in my example:

   	D	E	F	G - I	J
   1	DATE	AMOUNT	CATEGORY		CATEGORY DROPDOWN
   2	Jan 01, 2022	65.76	Entertainment		*All
   3	Dec 26, 2021	15.28	Grocery		Banking
   4	Dec 31, 2021	4.65	Banking		Entertainment
   5	Jan 04, 2022	56.04	Work		Grocery
   6	Jan 07, 2022	21.51	Grocery		Work
   7	Jan 13, 2022	35.28	Banking
   8	Jan 15, 2022	65.25	Work
   9	Jan 22, 2022	25.61	Banking
   10	Jan 23, 2022	5.36	Work

Lookup Sheet

This is name I gave the sheet you are performing your lookup in.

1. I left your lookup Month in C3

2. I left your lookup year in E3

3. I added a "Months to Report" field in G3, so if you want 12 months of reporting from the start date you would put 12 in G3.

4. I added a Category dropdown in A3, pulling the results from the new formula in Column J of Expenses (see #2 of the Expenses Sheet section above).

5. In A5 I placed a formula that will return the results of a lookup and I formatted it as follows:

   1. Row 5 contains the list of Months, "mmm yyyy", being returned.
   2. Subsequent rows each begin with the Category being returned in the first Column, A, and the total expenses for each Category in the appropriate Months column.

6. Basic layout of the sheet in my example:

   	D	E	F	G	H	I	J	K	L	M	N
   1
   2	**Select Category ↓ **		Lookup Month		Lookup Year		Months to Report
   3	*All		3		2023		8
   4
   5	CATEGORY	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023
   6	Banking	293.48	261.71	75.97	22.32	69.17	28.22	132.71	52.76	183.44	57.95
   7	Entertainment	68.82	103.12	124.43	97.03	87.13	198.05	53.26	0	126.4	127.13
   8	Grocery	151.7	189.88	84.03	105.16	59.67	0	156.95	73.68	56.9	34.3
   9	Work	0	36.71	76.43	65.73	247.28	131.31	68.88	60.31	14.23	157.01

Formula in Lookup!A5

=LET(e_,Expenses!D4:F, selCat,A3,
  cat,INDEX(e_,,3), amt,INDEX(e_,,2), dat,INDEX(e_,,1),
  mos,INDEX(EOMONTH(EOMONTH(DATE(E3, C3, 1),0),
    SEQUENCE(1,G3,0))), 
  uCat,IF(OR(selCat="", selCat="*All"),
    SORT(UNIQUE(TOCOL(cat,1))), selCat), 
  {{"CATEGORY"; uCat},{mos; BYCOL(mos, LAMBDA(mon,
    BYROW(uCat, LAMBDA(c, SUMPRODUCT(amt, EOMONTH(dat,0)=mon, 
        cat=c)))))}})

BYCOL  BYROW  DATE  EOMONTH  IF  INDEX  LAMBDA  OR  SEQUENCE  SORT  SUMPRODUCT  TOCOL  UNIQUE

Anonymous Test Sheet

I posted the sample data in an anonymous spreadsheet at this link.

Anyone can edit it anonymously so I recommend you make a local copy in case it gets damaged over time. If you prefer to play with the public copy, I would then suggest duplicating the sheets and renaming them for your use so multiple users don't edit the same sheets. Lookup Sheet

Expenses Sheet