Month To Date SUMIFS formula calculating incorrectly

[cmjay]

Hi,

I'm using the following SUMIFS formula to calculate a running MTD figure from data in column B based on dates in column J and names in column I:

=SUMIFS('RawRevenueData-(Nov)'!B:B,'RawRevenueData-(Nov)'!J:J,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),'RawRevenueData-(Nov)'!I:I,"Federal Projects")-SUMIFS('RawRevenueData-(Nov)'!B:B,'RawRevenueData-(Nov)'!J:J,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),'RawRevenueData-(Nov)'!I:I,"Federal Projects")

It looks as though it's running a month range rather than hard stops at the end of the month; for example, data in early February is included in today (january's) MTD total.

Anyone have a potential solution?

Thanks!

 

[godsaaint]

It looks as though it's running a month range rather than hard stops at the end of the month; for example, data in early February is included in today (january's) MTD total.

Not I sure follow what you are describing but right now you are summing all values with a greater or equal date than 01/01/2017 (first sumifs function) and subtracting it from it all values with a greater date than 02/01/2017. This is not your desired output? In any case, could you post a sample of your data?

 

[cmjay]

Not I sure follow what you are describing but right now you are summing all values with a greater or equal date than 01/01/2017 (first sumifs function) and subtracting it from it all values with a greater date than 02/01/2017. This is not your desired output? In any case, could you post a sample of your data?

I'm having trouble inserting a sample of the data; here's a basic version:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Revenue[/TD]
[TD]Misc[/TD]
[TD]Misc[/TD]
[TD]Misc[/TD]
[TD]Misc[/TD]
[TD]Misc[/TD]
[TD]Misc[/TD]
[TD]Salseperson[/TD]
[TD]Order Date[/TD]
[/TR]
[TR]
[TD]914P1090[/TD]
[TD]2,000[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Federal Projects[/TD]
[TD]1/1/17[/TD]
[/TR]
[TR]
[TD]cg508990[/TD]
[TD]4,000[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Federal Projects[/TD]
[TD]2/1/17[/TD]
[/TR]
</tbody>[/TABLE]

I would need a formula that returns a value of 2,000; the current formula returns 6,000.

Thanks!

 

[godsaaint]

Try:

=SUMIFS('RawRevenueData-(Nov)'!B:B,'RawRevenueData-(Nov)'!J:J,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),'RawRevenueData-(Nov)'!I:I,"Federal Projects",)'RawRevenueData-(Nov)'!J:J,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

 

[cmjay]

Try:

=SUMIFS('RawRevenueData-(Nov)'!B:B,'RawRevenueData-(Nov)'!J:J,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),'RawRevenueData-(Nov)'!I:I,"Federal Projects",)'RawRevenueData-(Nov)'!J:J,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

That formula is showing as an error.

Thanks for your help!

 

[godsaaint]

I put the ) in the wrong place, instead of the end. Try again:

=SUMIFS('RawRevenueData-(Nov)'!B:B,'RawRevenueData-(Nov)'!J:J,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),'RawRevenueData-(Nov)'!I:I,"Federal Projects",'RawRevenueData-(Nov)'!J:J,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))

 

[cmjay]

That worked perfect; thank you very much!