Try this
=SUM(OFFSET($A$2,MAX(IF($A$2:$A$200<"",ROW($A$2:$ A$200)-ROW($A$2)+1))-3,0,3
))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"karl41" wrote in
message ...
Hi all
Having a problem in excel which is driving me mad! I have a list of
data and i want to create a conditional sumif formula where the sum is
taken only of the last three entries. For our league analysis i look at
a team results history for the last three games by adding up the goals
for and against for last three matches. I want to write a formula which
would look through the list and automatically add the goals for the last
three entries for a team. This cant be done with a traditional sumif
formula as the range is specified and this may include the last four or
five games for a team whilst three for another.
Greatly appreciated,
Karl
complex criteria summing
--------------------------------------------------------------------------
------
Karl,
I did not look at your attachment, but if you have a list of dates,
then you can use a formula like
this...
Array enter (enter using Ctrl-Shift-Enter)
=SUM(IF(RANK(A2:A15,A2:A15)<4,B2:B15,0))
Where dates are in column A, and the numbers to be summed are in column
B.
HTH,
Bernie
MS Excel MVP
thanks for your reply, that would not work however as the criteria is
not date driven but is looking for the last three entries of the event
in two columns, i have attached the spreadsheet along with an example
of the current formula im using which is fixed in range so would return
incorrect values if more or less than my target number of events would
occur, thanks
+-------------------------------------------------------------------+
|Filename: help_g9394.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5192 |
+-------------------------------------------------------------------+
--
karl41
------------------------------------------------------------------------
karl41's Profile:
http://www.excelforum.com/member.php...o&userid=37473
View this thread: http://www.excelforum.com/showthread...hreadid=571404