SUMPRODUCT Function Between Two Dates

Posted on 2013-05-13
Last Modified: 2013-05-13
I am using the following function to return a value from Column B if Column A meets certain criteria:

=SUMPRODUCT(--('Brian Chadwick.xlsx'!$A2:$A200>20130505),'Brian Chadwick.xlsx'!$B2:$B200)

This function is working correctly.  However, I need to alter the function to test for a range of  numbers.  IE: >20130505 AND <20130513.  I've tried several variations of the formula with no success. Can anyone help me?
Question by:nquinn94
LVL 23

Accepted Solution

NBVC earned 500 total points
ID: 39161639
You tried?

=SUMPRODUCT(--('Brian Chadwick.xlsx'!$A2:$A200>20130505),--('Brian Chadwick.xlsx'!$A2:$A200<20130513),'Brian Chadwick.xlsx'!$B2:$B200)
LVL 39

Expert Comment

ID: 39161995
Or as a multiplication

=SUMPRODUCT(('Brian Chadwick.xlsx'!$A2:$A200>20130505)*('Brian Chadwick.xlsx'!$A2:$A200<20130513)*('Brian Chadwick.xlsx'!$B2:$B200))

Since you have excel 2010, you can also use SUMIFS

=SUMIFS('Brian Chadwick.xlsx'!$B2:$B200,'Brian Chadwick.xlsx'!$A2:$A200,">20130505",'Brian Chadwick.xlsx'!$A2:$A200,"<20130513")


Author Closing Comment

ID: 39162072
Worked perfectly!  Thank you!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question