Excel formula: Add entries in same month and year

debugcn Published at Dev

Brian Hamilton

This seems like it should have a fairly simple solution, but I can't quite find it. Any help would be much appreciated.

I'm trying to write a formula in Excel that will refer to an itemized list of income and expenses and add up all of the entries in a given month and a given category. So if I have the following entries in one sheet: 6/15/16 Electric -$80.00 Utilities 7/1/16 Landlord -$800.00 Rent 7/2/16 McDonald's -$5.00 Fast Food 7/15/16 Electric -$80.00 Utilities 7/19/16 Water -$55.00 Utilities

I want a formula that will select and add, for example, all of the utilities from July but not from June.

Currently, I can select for category but I can't figure out how to select for the month. Here's what I'm working with:

=SUMIFS(Spending!C:C,Spending!D:D,"=Utilities",Spending!A:A,"=MONTH(D1)&YEAR(D1)")

The Spending sheet is organized just like my above example, the C column refers to expenses, the D column to categories, and the A column to dates. D1 in the last test refers to the column heading, which names this column as having to do with July 2016 expenses.

One further question, too: I'd prefer to avoid hard-coding the second test, and instead make it refer to the row heading. So instead of saying "=Utilities", say "=A3"--i.e., the text value of the row header. But that way of writing it breaks the formula, and I can't figure out what else to try.

Thanks for any help!

Scott Craner

You need to bracket the dates with greater than and less than:

=SUMIFS(Spending!C:C,Spending!D:D,C1,Spending!A:A,">=" & DATE(YEAR(D1),MONTH(D1),1),Spending!A:A,"<=" & EOMONTH(D1,0))

For reference Spending sheet:

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-5

Comments

0 comments

From Dev

Related Related

Article

Excel formula: Add entries in same month and year

Excel formula: Add entries in same month and year

Excel formula to display ONLY month and year?

Excel formula to set "one month/year" before?

Add year month combination as decimals in excel

Excel Formula or VBA to convert year, month data to actual date

Formula to display project month & year number instead of calendar Month & Year in Excel

Excel month formula

SQL: How to get entries with the same month and year as the table's timestamp field?

Excel: Array formula to sum values based on matching month and year results in value error

How to add one month to month and year

Add entire calender year to pivotTable in Excel from sample with only few dated entries

How to add formula to Excel

Selecting entries with a specific year and month from a Rails database

Selecting entries with a specific year and month from a Rails database

Excel formula for cumulative costs in 5 year increments

Excel formula for declining number, in 5 year intervals

Excel Formula to output biweekly dates and end of month

What is the excel formula to find the upcoming Saturday a month?

excel shortcut for month day year short date

Excel formula to increase month by one and week in month by one

add one month in excel into the current month

How to get the difference of current month for the current year data and previous year same month in ssrs?

How to calculate the difference of a month of the current year and same month from previous year in SQL

Week of month, month of year,year

Is it possible to declare a variable within formula and using it into same formula in excel?

Unable to use the same formula for different rows in excel

same formula but different result in Matlab and Excel

Formula to convert quarter-year to MM/DD/YYYY in Excel

Excel TEXT formula doesn't convert 'yyyy' to a year

Excel formula - find if any date in a given range matches a given year