SUMIF for Current Month, Next Month, etc.

[GriffinCO]

Hello All,

I'd like to set up a chart to track data so I know what sales are pending for this month, next month, etc. Here's my data:

So what I'd like is a value for Total Sales for the Current Month, Total Sales for Next Month, etc. So when I add another sale to the table, it updates automatically.

Tried to build a formula =SUMIF(A:A,"="&MONTH(TODAY()),B:B) but that didn't work. I know I'm missing something in calculating the current month using a serial number value, since MONTH doesn't return a serial number like NOW does.

I'm sure there's a better way of writing this code. Any help is greatly appreciated!

Cheers,
Jamie

Excel 2007 XP

 

[Zack Barresse]

Hi there, welcome to the board!

These should work for you...

Sum this months figures, adjust range accordingly:

=SUMIF(A2:A6,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B2:B6)-SUMIF(A2:A6,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),B2:B6)

Sum next months figures, adjust range accordingly:

=SUMIF(A2:A6,">="&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),B2:B6)-SUMIF(A2:A6,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+2,1),B2:B6)

Edit: Also, by the way, if your table has a name, i.e. Table1, then you can use the table name definitions in your formula and it will always adjust accordingly...

This month:

=SUMIF(Table1[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),Table1[Sale Price])-SUMIF(Table1[Date],">"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),Table1[Sale Price])

Next month:

=SUMIF(Table1[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),Table1[Sale Price])-SUMIF(Table1[Date],">"&DATE(YEAR(TODAY()),MONTH(TODAY())+2,1),Table1[Sale Price])

HTH

 

[GriffinCO]

Hi Zack,

Thanks! Works like a champ.

How would I set it up so that it automatically incorporates any new sales I put into the table? Do I need to create some sort of dynamic named range? Or can I just use A:A, B:B?

Cheers!
Jamie

 

[Zack Barresse]

Sorry, I edited the post to include a dynamic set of formulas for your table, just adjust the table name.

 

[GriffinCO]

Thanks!