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:

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

  • Welcome to Web Applications Stack Exchange. Please add more details: 1) The spreadsheet locale / country in order to know what is the default date format, 2) Sample data for the ranges included in the formula and the expected result. Commented May 7 at 17:16
  • I edited your question based on your most recent comments. Please don't hesitate to correct anything I misunderstood, or roll back the edits entirely as you see fit.
    – Blindspots
    Commented May 8 at 16:18

2 Answers 2


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

Screenshot showing error popoup

Screeshot showing error popup

Array arguments to SUMIFS are of different size

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

     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))
  • Thanks. Unfortunately, that gave me an N/A error.
    – jbuck
    Commented May 7 at 17:40
  • You should review your data. Commented May 7 at 17:41
  • my data is literally just a list of dates in one column and prices in another column. I'm not sure what else there is to check. I did double-check that the date column was formatted as date and not plain text or anything else. What should I be reviewing for (I mean, what sort of thing would I be looking to review)?
    – jbuck
    Commented May 7 at 17:54
  • I'm an idiot...simple typo on my part from what you sent me (the =ArrayFormula)...that seems to be working. Thanks so much!
    – jbuck
    Commented May 7 at 17:57


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

   Expenses!D4:D>=DATE(E3, C3, D3),
   Expenses!D4:$D<=DATE(I3, G3, H3),


  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:

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


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:



  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
    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
    2 **Select Category ↓ ** Lookup Month Lookup Year Months to Report
    3 *All 3 2023 8
    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),
  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, 


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
Lookup Sheet

Expenses Sheet
Expenses Sheet

  • Thanks. March was just an example. I want to input a date range (generally 1 Jan 20xx through 31 Dec 20xx), then I have 12 rows, one for each month, and I want to get the total for each month. I actually have it broken down even more so I can see how much we spent on advertising each month of x year, how much on cable/internet, gas, electric, etc.
    – jbuck
    Commented May 8 at 13:01
  • I am getting a VALUE error with the first formula you have there.
    – jbuck
    Commented May 8 at 13:09
  • Sorry fixed. Forgot to update your code to remove the quotes and commas in the conditions.
    – Blindspots
    Commented May 8 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.