Problem getting < > operators to work in SUMIFS formula

[jthompso]

I have two columns of data (A dates and B currency amounts). The task I am trying to accomplish is simple: I want to sum up the transactions amounts (formatted as currency) in column B if they fall within a certain date range (dates are found in column A). I have two date fields: E4 and F4 containing 7/2/2014 and 7/16/2014.

=SUMIFS(B:B,A:A,">"&E4,A:A,"<"&F4)

I have spent a good deal of time trying to find out why it is returning zero, and I cannot find the answer. The goal is to take raw data like this and do a simple quarterly sum (the dates don't change). I suspect that the operators ">" and "<" are the problem, but that's just an uninformed opinion. I'm using Excel 2013, and sometimes 2007. Thanks for your help.

 

[Special-K99]

Maybe (untested)

=SUMPRODUCT((A:A > E4)*(A:A < F4)*(B:B))

 

[bbott]

I have two columns of data (A dates and B currency amounts). The task I am trying to accomplish is simple: I want to sum up the transactions amounts (formatted as currency) in column B if they fall within a certain date range (dates are found in column A). I have two date fields: E4 and F4 containing 7/2/2014 and 7/16/2014.

=SUMIFS(B:B,A:A,">"&E4,A:A,"<"&F4)

I have spent a good deal of time trying to find out why it is returning zero, and I cannot find the answer. The goal is to take raw data like this and do a simple quarterly sum (the dates don't change). I suspect that the operators ">" and "<" are the problem, but that's just an uninformed opinion. I'm using Excel 2013, and sometimes 2007. Thanks for your help.

Your formula worked when I tested it. What is in colum B and what is your expected outcome? Are you able to post a small amount of you data?

 

[jthompso]

I should be getting $167. Column B is currency.
Here are columns A and B.

[TABLE="width: 193"]
<tbody>[TR]
[TD]7/3/2014
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]7/8/2014
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/10/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]8/5/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/8/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]6/30/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/7/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/1/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/22/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/7/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/8/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/15/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/17/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/2/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/14/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]8/5/2014
[/TD]
[TD]20
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jthompso]

The SUMPRODUCT solution also returned "0"

 

[bbott]

I should be getting $167. Column B is currency.
Here are columns A and B.

[TABLE="width: 193"]
<TBODY>[TR]
[TD]7/3/2014
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]7/8/2014
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/10/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]8/5/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/8/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]6/30/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/7/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]7/1/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/22/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/7/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/8/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]7/15/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/17/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/2/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/3/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]7/14/2014
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]8/5/2014
[/TD]
[TD]20
[/TD]
[/TR]
</TBODY>[/TABLE]

When I copy and paste this data into a sheet, I get 167 from your formula. And there are definitely dates in E4 and F4 in your worksheet, correct? Are any of your dates in column A entered as text with a leading apostrophe ('7/12/2014, '7/13/2014)?

 

[jthompso]

Yes, there are definitely dates in E4 (7/2/2014) and F4 (7/16/2014). They are not formatted as text, nor are the dates in Column A formatted as text.

 

[vds1]

Before you try out the formulas. Select Column A

Goto Data > Text To Column > Select Delimited > Check Tab uncheck rest ,Next > Select Date , in drop down select MDY > Finish.

Then try the above solutions.

 

[jthompso]

That worked! Thank you very much.

However, I don't understand why. Can you explain it a little more? This sheet is going hold the raw data exported from our database, and the formula will already be pre-entered so the reader can open and see it already calculated.

Before you try out the formulas. Select Column A

Goto Data > Text To Column > Select Delimited > Check Tab uncheck rest ,Next > Select Date , in drop down select MDY > Finish.

Then try the above solutions.

 

[vds1]

That worked! Thank you very much.

However, I don't understand why. Can you explain it a little more? This sheet is going hold the raw data exported from our database, and the formula will already be pre-entered so the reader can open and see it already calculated.

How To Convert Wrongly Formatted Dates - FREE Microsoft Excel Spreadsheet Tips and Tutorials