Conditional Count Formula
 

If your up for a challenge please have a go at this formula as I'm stumped!

What I want to do is create a formula that will analyze a historical table
of sports games and count how many bets it takes to record a win from a set
of rules that I define.

The bets are only counted if it meets this set of rules, otherwise it doesnt
get counted. Also only one bet can be counted per unique date so if more
than two games meet the rules for a bet on a particular date, than it is the
game with the lower odds (ColD) that gets counted.

I have included the data table below from columns A - E. Column F is where
the formula displays the bets counted when a win takes place that meets my
rules.

I have included comments in brackets for column F which are not part of the
formula, its simply to explain how the formula would interpret the data shown
from columns A - E.

However the data in column F with a number enclosed in " " is the data I
want the formula to display.

As you can see from the table below, it took 3 bets (including the win)
before I posted my first win, hence the number "3" is displayed in column F.
It takes another 2 bets before I get my next win, so number "2" is displayed.

The rules for counting a bet are as follows.

* Have to be the lowest odds (ColD) but than $1.40 and < $1.60.
* Be a home game (ColA).
* There can only be one bet per unique date, lowest odds is selected.
* In the advent of same odds & same date, the team with the lowest
alphabetical ranking (ColB) should be counted.

SAMPLE DATA & RESULTS COLUMN
============================

A==B=======C=========D======E======F============== =================
H Team_a 30/12/07 $1.70 LOSS - (no cnt as $1.60)
H Team_b 30/12/07 $1.42 DRAW - (1st cnt - rules met)
H Team_b 30/12/07 $1.47 WIN - (no cnt same date & higher odds)
H Team_d 01/01/08 $1.45 WIN - (2nd cnt - rules met)
A Team_e 02/01/08 $1.54 WIN - (no cnt - away game)
H Team_f 02/01/08 $1.30 WIN - (no cnt as < $1.40)
H Team_g 03/01/08 $1.50 WIN - "3"
H Team_h 03/01/08 $1.52 WIN - (no cnt as same date & higher odds)
H Team_i 20/01/08 $1.53 DRAW - (1st cnt - rules met)
H Team_k 22/01/08 $1.53 WON - "2"

So far another helpful person has come up with this formula

=IF(A2="H",IF(E2="WON",IF((D21.4)*(D2<1.6),
SUM(IF(FREQUENCY(IF(($A$1:A2="H")*($D$1:D21.4)*($ D$1:D2<1.6)*
($C$1:C2<"")*($E$1:E2<"WON"),MATCH("~"&$C$1:C2,$ C$1:C2&"",0)),
ROW($C$1:C2)-ROW($C$1)+1),1))-SUM($K$1:K1),""),""),"")

It almost works, but it counts multiple bets on same date when they are both
wins when it should just choose the one win that has lowest odds or if same
odds then the lowest alphabet ranking for the team name (ColB).

Conditional Count Formula
 

Please show Domenic some respect, he has been helping you extensively in
your post he 'Complex Formula need help with - Page 2 - MrExcel
Message Board'
(http://www.mrexcel.com/forum/showthr...=397801&page=2)
under a different ID.

Please continue there only... I also noted that you now understood
cross-posting rules per you comments he 'Sports Tipping Formula -
Excel Help Forum' (http://tinyurl.com/nna6lj)


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110054

Conditional Count Formula
 

Hi,

I'm not sure that cross posting between unconnected websites is necessarily
bad, if that is what's going on here. Does everything posted at Mr Excel
automatically post to the Microsoft Newsgroups?

=====
Multi conditional counting can be done with any of the following types of
formulas
1. =SUMPRODUCT((B$1:B$7=F1)*(A$1:A$7=E1))
2. =SUMPRODUCT(--(B$1:B$7=F1),--(A$1:A$7=E1))
3. The array entered equivalents of the above two
4. =COUNT(IF(A$1:A$7&B$1:B$7=E1&F1,)) (array entered)
5. =COUNTIFS(B$1:B$7,F1,A$1:A$7,E1) (in 2007)

in each case B1:B7 and A1:A7 are the ranges you are checking for the
conditions in E1 and F1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"NBVC" wrote:


Please show Domenic some respect, he has been helping you extensively in
your post he 'Complex Formula need help with - Page 2 - MrExcel
Message Board'
(http://www.mrexcel.com/forum/showthr...=397801&page=2)
under a different ID.

Please continue there only... I also noted that you now understood
cross-posting rules per you comments he 'Sports Tipping Formula -
Excel Help Forum' (http://tinyurl.com/nna6lj)


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110054

Conditional Count Formula
 

Consider yourself lucky if you have Domenic helping you. If Domenic can't
figure it out there's a good chance that no one will be able to figure it
out.

--
Biff
Microsoft Excel MVP


"NBVC" wrote in message
...

Please show Domenic some respect, he has been helping you extensively in
your post he 'Complex Formula need help with - Page 2 - MrExcel
Message Board'
(http://www.mrexcel.com/forum/showthr...=397801&page=2)
under a different ID.

Please continue there only... I also noted that you now understood
cross-posting rules per you comments he 'Sports Tipping Formula -
Excel Help Forum' (http://tinyurl.com/nna6lj)


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110054