SumIf Question

B

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
 
B

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.
..
 
O

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
 
B

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.
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top