Hello I have the following results:
A1
-3
9
15
0
0
-3
-3
-3
0
0
12
-3
0
0
0
0
0
0
-3
15
0
0
0
These are numbers from a card game. If it is a 0 then the player didn't play.
I want to sum the last 5 games where the player did play.
So the result should be -3 +12 -3 -3 +15= 18
What formula do i need to use?
Try this array formula**:
=SUMIF(INDEX(A1:A25,LARGE(IF(A1:A25<>0,ROW(A1:A25)-ROW(A1)+1),5)):A25,"<>0")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Another way......
Try this Array Formula, which requires confirmation with Ctrl+Shift+Enter instead of just Enter.
Assuming that your numbers are in the range A1:A23, then
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Thanks, exactly what i was looking for.
great formula
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks