complex criteria summing

[karl41]

Hi all
Having a problem in excel which is driving me mad! I have a list o
data and i want to create a conditional sumif formula where the sum i
taken only of the last three entries. For our league analysis i look a
a team results history for the last three games by adding up the goal
for and against for last three matches. I want to write a formul
which would look through the list and automatically add the goals fo
the last three entries for a team. This cant be done with a traditiona
sumif formula as the range is specified and this may include the las
four or five games for a team whilst three for another.

Greatly appreciated,
Kar

 

[Bernie Deitrick]

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

 

[karl41]

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=5191 |
+-------------------------------------------------------------------+

 

[Bernie Deitrick]

karl,

Again, I have NOT downloaded your example workbook. Few people will.

These formulas will return the last three entries of column C, within the bounds of C1:C1000. They
are array formulas, and need to be entered using Ctrl-Shift-Enter, rather than just Enter.

=INDEX(C:C,MAX(IF(C1:C1000<>"",ROW(C1:C1000),0)))
=INDEX(C:C,LARGE(IF(C1:C1000<>"",ROW(C1:C1000),0),2))
=INDEX(C:C,LARGE(IF(C1:C1000<>"",ROW(C1:C1000),0),3))

You could make a table of these formulas by copying to the right....

HTH,
Bernie
MS Excel MVP

 

[Alan]

There's probably an easier and more elegant way to do it, but this works,
=SUM(INDIRECT(ADDRESS(MATCH(1000,A1:A1000)-2,1,4)&":"&ADDRESS(MATCH(1000,A1:A1000),1,4)))
Regards,
Alan.