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:

Order #	Revenue	Misc	Misc	Misc	Misc	Misc	Misc	Salseperson	Order Date
914P1090	2,000	x	x	x	x	x	x	Federal Projects	1/1/17
cg508990	4,000	x	x	x	x	x	x	Federal Projects	2/1/17

<tbody>
</tbody>

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!