Sumif by month and year

[Josh Hendrickson]

I have a list of revenue broken down by transaction for the past four years.
I want to see the total amount of revenue generated during the month of
December 2006 v. December 2008. Right now, my dates on the spreadsheet are
in the form 12/31/2006. What type of formula should I use to do this?

I have tried this but it is coming up with nothing and I can see that there
were sales during the month of December 2006.

=SUMIFS(M:M,I:I,"<=(12/31/2006)",I:I,">=(12/01/2006)")

Thank you for all of your help offered.

 

[T. Valko]

Use cells to hold your date boundaries:

A1 = 12/1/2006
B1 = 12/31/2006

=SUMIFS(M:M,I:I,">="&A1,I:I,"<="&B1)

 

[Josh Hendrickson]

Thank you for the response T. Valko. I was hoping to do this for every month
from 2006 to 2009. Do I have to put each date into a separate cell?

 

[T. Valko]

You don't have to but then how would you identify a particular result?

Try this:

If you want to start from 1/1/2006...

Enter this formula in A2:

=DATE(2006,ROWS(A$2:A2),1)

Enter this formula in B2:

=SUMIFS(M:M,I:I,">="&A2,I:I,"<="&EOMONTH(A2,0))

Select both A2 and B2 and copy down as needed.

A2:An will return the 1st of the month for each month/year.

 

[Josh Hendrickson]

Thank you for all your help. I wound up figuring it out by manually writing
in the dates. I am not sure why the formula I originally used did not work
in the first place but when I added the $ signs, it seemed to work just fine.
This is what I wound up using to achieve the desired result:

=SUMIFS($M:$M,$I:$I,">=1/1/2006",$I:$I,"<=1/31/2006")

Once again,

Thank you

 

[T. Valko]

That'll work but doing it that way you have to *manually* enter each range
of dates for each formula for each month you want a result.

Trust me, it's a lot easier to use cells to hold the dates!

 

[Josh Hendrickson]

Your advice is invaluable! I started doing in manually and it got very
tedious. I was able to quickly do it your way and am now up and running like
a champion. Thank you for all of your help!

 

[T. Valko]

Good deal. Thanks for the feedback!