Didnt work- 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

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.

Didnt work- Complex Criteria Summing ;(
 

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

Didnt work- Complex Criteria Summing ;(
 

I have provided a set of formula that should do what you want in the
attached copy of your spreadsheet. There are many helper columns and
some of the formulas are quite long, so I think it would be rather
complicated to spell them out here.

It may be possible to provide the answer in a few simpler formulas, but
this method seems to work, and I leave the more elegant solution to
other folks. You could combine a number of the columns if you wish - I
left them separate so that you could follow the logic of the solution.
You may also wish to put in tests to determine if fewer than three
matches have been played by a club - I ignored that situation and just
started the formulas after at the third match.

The columns I coded are all in green.

I hope this helps - let me know if it works for you.

Declan O'R


+-------------------------------------------------------------------+
|Filename: help_g9394_DOR.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5195 |
+-------------------------------------------------------------------+

--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=571404