Count with two criteria

**SpareBank1** · 10-06-2009, 07:20 AM

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** · 10-06-2009, 07:48 AM

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** · 10-06-2009, 10:42 AM

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** · 10-06-2009, 10:50 AM

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** · 10-06-2009, 11:16 AM

Originally Posted by 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.

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

**JBeaucaire** · 10-06-2009, 11:21 AM

I have the "Office 2007" package, so i expected it was Excel2007, but when i check it it says Microsoft Excel 2002

Be sure to edit your profile to match.

**DonkeyOte** · 10-06-2009, 11:23 AM

The formula provided previously does what you want - you may need to alter the delimiters per your locale settings.
I had assumed from your profile

that you were in Norway and thus would be using ; delimiter - if not adjust my example as necessary.
(note however in your sample file Col X does not contain any 1.60 values so you will get a 0 output).