Count with two criteria

[SpareBank1]

Hi,
I have a dataset with odds for soccermatches, and results.
I want to count each time a specified odds has won the match, but i cannot find out which formula to use.

For example:

HTeam ATeam Res H-Odds D-Odds A-Odds
1.Team1 Team2 H 1.60 2.10 4.30
2.Team1 Team2 D 1.64 2.30 3.30
3.Team1 Team2 H 1.60 2.60 4.35
4.Team1 Team2 A 3.10 2.80 1.60
5.Team1 Team2 A 1.60 2.10 3.30
6.Team1 Team2 D 1.60 1.60 4.30

HTeam= Home team
ATeam= Away team
Res = Result, Home, Draw, or Away
H-Odds= Home odds
D-Odds= Draw odds
A-Odds= Away odds

Defining my problem:
I want to count the times the odds; 1.60 was the winning odds.
I imagine i need to set up 3 columns: Homewins, Draws, Awaywins.

Kind of like:
=COUNTIF(A1:A50,"1.60"),IF(B1:B50,"H")
like that, only that is not correct

Can anyone help me?

[whallgren]

=SUM((A1:A50="1.60")*(B1:B50+"H")

CTRL + Shift + Enter

The array way.

[SpareBank1]

hmm maybe i misunderstood the formula, but it didnt work

Lets say:
The Results (H, D, or A) is displayed from G2 to G381 (G2:G381)
The Home odds is displayed from X2 to X381 (X2:X381)

So if i want to know: How many times did the Home-odds 1.60 win?
Could you please explain more specific like if I were stupid?

Like: How many times does 1.60 appear in X2:X381, when "H" is in G2:G381

What does CTRL+SHIFT+ENTER do?

[SpareBank1]

Come on, can anyone please help me with this? At least tell me if its possible / not possible. It can be others sollutions than counting? Just ways to do it? I'm on a time schedule and some tips would be very very helpful

[DonkeyOte]

Based on the below

Lets say:
The Results (H, D, or A) is displayed from G2 to G381 (G2:G381)
The Home odds is displayed from X2 to X381 (X2:X381)

So if i want to know: How many times did the Home-odds 1.60 win?
Could you please explain more specific like if I were stupid?

Like: How many times does 1.60 appear in X2:X381, when "H" is in G2:G381

Using XL2007

=COUNTIFS(X2:X381;1.60;G2:G381;"H")

[SpareBank1]

Thanx alot for your help, but still i can't get it to work
I'll add the sheet here, and the actual cells are the ones i mentioned X2:X381 and G2:G381, so you can see for yourself I tried your formula but it says the formula contains an error. Tried with both COUNTIFS and COUNTIF (couldn't find COUNTIFS in the formula-list). There is a COUNT, a COUNTA, and a COUNTIF. COUNTA is counting number of blank cells.

Heres the file:
http://www.football-data.co.uk/mmz4281/0910/E0.csv

[DonkeyOte]

OK your profile says you're using XL2007 - if you can't see COUNTIFS I suspect you're actually running an earlier version.

Does the below work ?

=SUMPRODUCT(--(X2:X381=1.60);--(G2:G381="H"))

Going forward please attach files here directly.

[SpareBank1]

OK your profile says you're using XL2007 - if you can't see COUNTIFS I suspect you're actually running an earlier version.

Does the below work ?

=SUMPRODUCT(--(X2:X381=1.60);--(G2:G381="H"))

Going forward please attach files here directly.

Oh huge mistake by me there. I have the "Office 2007" package, so i expected it was Excel2007, but when i check it it says Microsoft Excel 2002.
Sorry about that

I'll attach the file here:

http://www.football-data.co.uk/mmz4281/0809/E0.csv
Odds in: X2:X381 and Results in G2:G381, as in the example above

[SpareBank1]

Ahhhhh, now I understand what you were doing:P Stupid me, please don't laugh Than you for the Help as usual Well atm there are actually no more problems, so again, thank you so much

[SpareBank1]

Ok, new problem just arrived.

If I want to copy a formula every 5th column, and still make it count +1cell. How is it done?

For example, this is how it is if i copy them now:
A4=B2/B3
F4=G2/G3
J4=K2/K3

But i want them like this:
A4=B2/B3
F4=C2/C3
J4=D2/D3

So that the cells in the formula is +1cell, even if the formula moves +5 cells. Is this possible? How?
Thanx in advance
Thing is i have 800 formulas to do it with so i don't want to do it manually


EDIT: Nevermind, I've made a new thread concerning this issue since it is off topic