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.

7/3/2014	1
7/8/2014	1
7/3/2014	10
7/3/2014	10
7/10/2014	10
8/5/2014	10
7/8/2014	10
6/30/2014	10
7/7/2014	10
7/3/2014	10
7/1/2014	15
7/22/2014	15
7/7/2014	15
7/8/2014	15
7/3/2014	15
7/15/2014	20
7/17/2014	20
7/2/2014	20
7/3/2014	20
7/14/2014	20
8/5/2014	20

<tbody>
</tbody>

 

[jthompso]

The SUMPRODUCT solution also returned "0"

 

[bbott]

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

7/3/2014	1
7/8/2014	1
7/3/2014	10
7/3/2014	10
7/10/2014	10
8/5/2014	10
7/8/2014	10
6/30/2014	10
7/7/2014	10
7/3/2014	10
7/1/2014	15
7/22/2014	15
7/7/2014	15
7/8/2014	15
7/3/2014	15
7/15/2014	20
7/17/2014	20
7/2/2014	20
7/3/2014	20
7/14/2014	20
8/5/2014	20

<TBODY>
</TBODY>

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