CODEWITHSUNDEEP
excelsumifs
JohnN
JohnN

Reputation: 1010

Excel SUMIFS function not returning results consistently

I have a spreadsheet that I use to keep track of my checking account, budget and spending habits. I have a list of my financial transactions from my bank that I put in a table that looks like this (All dates and amounts are random):

Budget Example

I populate a budget chart and table that looks something like this:

Budget Table

Budget Chart

For awhile, I've been manually changing the fill of the Category tabs and manually populating the budget table. However, I realized that by adding conditional format rules that change the fill and text color based on specific text in a cell, I can add text like "Housing" or "Automotive" to the Category column and it would not only manually change the fill for me but it would also allow me to automatically populate the budget table. I tried using the SUMIFS function to do this.

Here's the way I tried to implement the SUMIFS function in the "Housing" cell for January in the Budget Table:

=SUMIFS('Sheet 1'!$D$2:$D$1000, 'Sheet 1'!$A$2:$A$1000, $A4, 'Sheet 1'!$B$2:$B$1000, ">01/01/2016", 'Sheet 1'!$B$2:$B$1000, "<01/31/2016")

This works for January and May, but when I use the same formula for February:

=SUMIFS('Sheet 1'!$D$2:$D$1000, 'Sheet 1'!$A$2:$A$1000, $A4, 'Sheet 1'!$B$2:$B$1000, ">02/01/2016", 'Sheet 1'!$B$2:$B$1000, "<02/29/2016")

It returns a value of 0 to the column. It does this for all other months. Any idea why this formula might not be working for me?

EDIT: Incase my forumla wasn't clear, I wanted to provide a break down. The SUMIFS formula is supposed to be summing all values in the Amount column (the 2:1000 is to have enough of an area to look for that it will cover a year of my regular updates to this table without having to update the search area) that fall into the specified criteria. Those criteria being the text "Housing" being in the "Category" column (which is following the conditional formatting rules so fill and text color are changed) and the value falls into the range of dates provided.

Upvotes: 1

Views: 640

Answers (1)

CallumDA
CallumDA

Reputation: 12113

Replacing:

< with <= and > with >=

will bring your values into the correct range

Upvotes: 2

Related Questions