Check out some similar questions!

feegs · May 17, 2012, 11:04 AM

Hi,
I'm stuck on a formula, I think it's a countif function but I'm not sure.
I've got a horse racing spreadsheet & I want it to read how many times my horse has won over a set distance... e.g.

1 1/2 mile 1
1 1/2 mile 2
1 1/4 mile 4
1 mile 3
1 1/2 mile 1

I want it to read that its won twice over 1 1/2 mile. How do I put that as a fomula?

Steve.

ebaines · May 17, 2012, 01:49 PM

One way is to use the "SUM" function as an array. The following would do it for you - in this example the distances of the races are assumned to be listed in cells A1 through A6 and the corresponding finishing places in cells B1 through B6:

{=SUM((A1:A6="1 1/2 mile")*(B1:B6=1))}

To make this work you do not actually type the beginning and ending curly braces, but rather after typing the rest of the formula you hit CNTL-SHIFT-ENTER (rather than just plain ENTER), and that tells Excel to treat this as an array formula. Excel then adds the curly braces. If you type the braces yourself it won't work.

Of course you can use cell references in place of the hard-coded condition; so for example if cell a10= "1 1/2 mile" and cell b10 = 1 this would work:

{=SUM((A1:A6=A10)*(B1:B6=B10))}

That way you could simply type the conditions that you're trying to count in cells A10 and B10 and not have to edit the formula.

Hope this helps!

feegs · May 18, 2012, 08:40 AM

Originally Posted by ebaines

One way is to use the "SUM" function as an array. The following would do it for you - in this example the distances of the races are assumned to be listed in cells A1 through A6 and the corresponding finishing places in cells B1 through B6:

{=SUM((A1:A6="1 1/2 mile")*(B1:B6=1))}

To make this work you do not actually type the beginning and ending curly braces, but rather after typing the rest of the formula you hit CNTL-SHIFT-ENTER (rather than just plain ENTER), and that tells Excel to treat this as an array formula. Excel then adds the curly braces. If you type the braces yourself it won't work.

Of course you can use cell references in place of the hard-coded condition; so for example if cell a10= "1 1/2 mile" and cell b10 = 1 this would work:

{=SUM((A1:A6=A10)*(B1:B6=B10))}

That way you could simply type the conditions that you're trying to count in cells A10 and B10 and not have to edit the formula.

Hope this helps!

That worked great, thanks!
Another one for you, same spreadsheet but can you do it on averages as well?
e.g...
1 1/2 mile 6
1 1/4 mile 2
1 1/2 mile 1
1 1/8 mile 3
1 1/2 mile 2

How could I get it to add the '1 1/2 mile' cells with the position it came in & give me an average so I'm looking for the answer of 3.

ebaines · May 18, 2012, 08:52 AM

There may be a more elegant way, but this works:

{=SUM((A1:A7="1 1/2 mile")*(B1:B7))/SUM((A1:A7="1 1/2 mile")*1)}

JBeaucaire · May 18, 2012, 05:32 PM

If you're on Excel 2007 or greater, there are simpler non-array options, too.

Your original question would use COUNTIFS

=COUNTIFS(A1:A7, "1 1/2 mile", B1:B7, 1)

Your second question would use AVERAGEIF:

=AVERAGEIF(A1:A7, "1 1/2 mile", B1:B7)