complex criteria summing
 

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


--
karl41
------------------------------------------------------------------------
karl41's Profile: http://www.excelforum.com/member.php...o&userid=37473
View this thread: http://www.excelforum.com/showthread...hreadid=571341

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


"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


--
karl41
------------------------------------------------------------------------
karl41's Profile: http://www.excelforum.com/member.php...o&userid=37473
View this thread: http://www.excelforum.com/showthread...hreadid=571341

complex criteria summing
 

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

--
karl41
------------------------------------------------------------------------
karl41's Profile: http://www.excelforum.com/member.php...o&userid=37473
View this thread: http://www.excelforum.com/showthread...hreadid=571341

complex criteria summing
 

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


"karl41" wrote in message
...

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

--
karl41
------------------------------------------------------------------------
karl41's Profile: http://www.excelforum.com/member.php...o&userid=37473
View this thread: http://www.excelforum.com/showthread...hreadid=571341

complex criteria summing
 

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


--
karl41
------------------------------------------------------------------------
karl41's Profile:
http://www.excelforum.com/member.php...o&userid=37473
View this thread: http://www.excelforum.com/showthread...hreadid=571341