Didnt work- 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 formula whic
would look through the list and automatically add the goals for the las
three entries for a team. This cant be done with a traditional sumi
formula as the range is specified and this may include the last four o
five games for a team whilst three for another.

Greatly appreciated,
Karl

Re: 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 colum
B.

HTH,
Bernie
MS Excel MVP



thanks for your reply, that would not work however as the criteria i
not date driven but is looking for the last three entries of the even
in two columns, i have attached the spreadsheet along with an exampl
of the current formula im using which is fixed in range so would retur
incorrect values if more or less than my target number of events woul
occur, thank

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

 

[Bob Phillips]

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)

 

[DOR]

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

It may be possible to provide the answer in a few simpler formulas, bu
this method seems to work, and I leave the more elegant solution t
other folks. You could combine a number of the columns if you wish -
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 thre
matches have been played by a club - I ignored that situation and jus
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'

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