SumIf Question

[BobA]

This formula will sum all of the values in column F for the last X number of CALENDAR DAYS. Let's say I put a 7 in cell J6. It will go back the last 7 CALENDAR days and sum the values in column F.

=SUMIF(B:B,">"&MAX(B:B)-J6,F:F)

I want to change this so instead of calendar days it just sums the last 7 days whatever they happen to be. Also, there could be multiple entries for the same day.

July 22 might have three entries, but it will still be treated as just one day.

Thanks

 

[Ron Rosenfeld]

This formula will sum all of the values in column F for the last X number of CALENDAR DAYS. Let's say I put a 7 in cell J6. It will go back the last 7 CALENDAR days and sum the values in column F.

=SUMIF(B:B,">"&MAX(B:B)-J6,F:F)

I want to change this so instead of calendar days it just sums the last 7 days whatever they happen to be. Also, there could be multiple entries for the same day.

July 22 might have three entries, but it will still be treated as just one day.

Thanks

Might there be missing dates that need to be excluded from the "Last 7"?

If so, then the following should work.

NAME your date range with a dynamic name.

e.g. If your dates are in column B, and B1 is a TEXT label, and the dates start in B2, then

Dates Refers To: =offset($B$1,1,0,count($B:$B))

You can now use this ARRAY-ENTERED formula:

=SUM(OFFSET(Dates,LARGE(IFERROR((FREQUENCY(
Dates,Dates)>0)*ROW(Dates),0),J6)-1,4,ROWS(
Dates)-LARGE(IFERROR((FREQUENCY(
Dates,Dates)>0)*ROW(Dates),0),J6)+1))

Note in the formula above that the columns argument of the OFFSET function is set to "4". This is the "distance" from B to F, per your example. You may need to adjust that if your example is not representative.
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[BobA]

B F J
7/23/13 $40 3
7/24/13 $-120
7/24/13 $75
7/26/13 $310
7/26/13 $-30
7/29/13 $80

The list is not necessairly consecutive, and could look something like the above.

So, if insert a 3 into cell J6 then it will sum the entries for the last three days in the column, (not the last three calender days), which in this case would be the last five entries, the 24th, 26th and 29th.
..

 

[Ookla the Mok]

You can use the following array formula. It must be entered by holding Control+Shift while pressing Enter.

=IFERROR( SUM( IF( List.Rows >= MIN( LARGE( List.Rows, ROW( INDIRECT( "1:" & $J$1 ) ) ) ), List.Values, 0 ) ), SUM( List.Values ) )

The formula uses the following Named Ranges...
List.Rows = ROW( INDEX( List.Values, 0, 1 ) ) - ROW( INDEX( List.Values, 1, 1 ) ) + 1
List.Values = $F$1:$F$6

• You need to point List.Values to whatever range holds the values you want to sum.
• The formula will ignore #'s entered in $J$1 that exceed the row count in List.Values and return the total of all rows instead.

Regards,
Gabriel

 

[Ron Rosenfeld]

B F J
7/23/13 $40 3
7/24/13 $-120
7/24/13 $75
7/26/13 $310
7/26/13 $-30
7/29/13 $80

The list is not necessairly consecutive, and could look something like the above.

So, if insert a 3 into cell J6 then it will sum the entries for the last three days in the column, (not the last three calender days), which in this case would be the last five entries, the 24th, 26th and 29th.

OK. Here is a simpler formula, using a similar algorithm and independent of the starting row of Dates. It does require that there be no empty rows in the Dates field. If there might be, the formula will need to be changed.

In the formula below, Dates must be defined as a dynamic range, and the $B$6 is the first cell in which the Dates data appears. So if the list of dates started in B2, you would enter that address there.

Dates Refers To: =OFFSET(Sheet1!$B$6,0,0,COUNT(Sheet1!$B:$B))
^^^


This formula must be **array-entered**:

=SUM(OFFSET(Dates,LARGE(IFERROR((FREQUENCY(Dates,Dates)>0)*ROW(Dates),0),J6)-ROW(Dates),4))

Again, in the formula above, the "4" (at the end) refers to the "column offset" between the Date column and the values to sum. So in your example, with dates in B and values to sum in F, that number is a 4.

 

[BobA]

Hi, and thanks for your help. I finally got your formula to work. It's been a long time since I've used a name range, and I like to avoid that if I can.

However, your formula does the exact same thing as this formula without having to use named ranges.

=SUM(OFFSET($F$1,LOOKUP(2,1/ISNUMBER($F:$F),ROW($F:$F))-$J$3,0,$J$3))

The problem is that neither formula takes into account multiple entries forthe same date. So, if I want to sum the profits for the last two days that I worked, and I have two entries for July 7, 2013 and one entry for July 8 and I enter a 2 into cell J1, neither formula will sum all three entries--only the last two.

 

[Ron Rosenfeld]

Dates must be defined as a dynamic range,

Dates could also be a fixed range, but it must refer to just the range of dates, and must start with the first data cell in the range; and cannot refer to an entire column.
So if your data was in b6:b11, you could use B6:Bn, but not B1:Bn or B:B. In addition "n" must be less than the the maximum number of rows in the worksheet by an amount equal to the number of data rows in the date range.

 

[BobA]

And we have a winner! I couldn't get it to work until I changed Sheet1! to the name of my actually sheet. Then it worked fine.

Once again, thank you Ron, and much thanks for all of the other responses.

 

[Ron Rosenfeld]

And we have a winner! I couldn't get it to work until I changed Sheet1! to the name of my actually sheet. Then it worked fine.

Once again, thank you Ron, and much thanks for all of the other responses.

Glad to help. Thanks for the feedback.