# SUMPRODUCT Date Range

1. ## SUMPRODUCT Date Range

I currently have a formula that is returning a value for a specific date from column A
=SUM(SUMPRODUCT(--(BAR!\$A\$1:\$A\$9022=V\$2)*(BAR!\$AK\$1:\$AK\$9022=\$A58),BAR!\$K\$1:\$K\$9022))

I need to update this formula to return a value searching date ranges from two columns (column G begin date and column H end date)

2. ## Re: SUMPRODUCT Date Range

Try this...

Use cells to hold the date criteria.

A1 = start date
B1 = end date

=SUMIFS(BAR!\$K\$1:\$K\$9022,BAR!\$A\$1:\$A\$9022,V\$2,BAR!\$AK\$1:\$AK\$9022,\$A58,BAR!\$G\$1:\$G\$9022,">="&A1,BAR!\$H\$1:\$H\$9022,"<="&B1)

3. ## Re: SUMPRODUCT Date Range

Try...

=SUMPRODUCT(--(BAR!\$A\$1:\$A\$9022=V\$2)*(BAR!\$AK\$1:\$AK\$9022=\$A58))*(BAR!\$G\$1:\$G\$9022>=Date)*(BAR!\$H\$1:\$H\$9022<=Date),BAR!\$K\$1:\$K\$9022))

4. ## Re: SUMPRODUCT Date Range

Hi -

A sample spreadsheet would really help get to an answer for you. If you could post some sample data with a couple examples of your intended outcome that would be REALLY helpful. Are you comparing to a single cell in column G and Column H or are you comparing the entire column of dates of both G and H to Columns A and AK?

Based on the information you have given, assuming a single cell in G and H, I would say your formula would look something like:

=SUMPRODUCT((BAR!\$A\$1:\$A\$9022>=G?)*(BAR!\$A\$1:\$A\$9022<=H?)*(BAR!\$AK\$1:\$AK\$9022>=G?)*(BAR!\$AK\$1:\$AK\$9022<=H?),BAR!\$K\$1:\$K\$9022)

As a side note there are a couple redundancies in your formula that I have removed from the above solution. First, I don't understand why you need SUM of the SUMPRODUCT. It will work, but it doesn't really do anything. Second, since you are multiplying your two conditions (Column A = V2 times Column AK = A58) you don't need the unary operator (the two dashes --). Doesn't hurt anything, but it's redundant. Again, just some picky stuff.

Hope this helps.

5. ## Re: SUMPRODUCT Date Range

This operation can be done with the more efficient SUMIFS function.

6. ## Re: SUMPRODUCT Date Range

Agree with Tony, especially is this is being used over a large range

There are currently 1 users browsing this thread. (0 members and 1 guests)