# Excel Formula

Asked May 17, 2012, 11:04 AM — 4 Answers
Hi,
I'm stuck on a formula, i think its 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 Posts: 10,040, Reputation: 5534 Expert #2 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 Posts: 7, Reputation: 10 New Member #3 May 18, 2012, 08:40 AM
Quote:
 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 Posts: 10,040, Reputation: 5534 Expert #4 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 Posts: 5,373, Reputation: 5036 Software Expert #5 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)

## Check out some similar questions!

Excel formula [ 15 Answers ]

Download Attachment, first. I need: -If B14=empty, then A and D colones both empty -If only B14=selected, then A14=TREZOR, D14=1, D15=1, bold underline between C14-L14 and C15-L15, others A and D empty -If B14=selected and B15=selected, then A14=TREZOR, A15=-II-, D14=1, D15=1, D16=2,...

I need a excel formula [ 2 Answers ]

Hi I need a excel formula for numbers ( Figures) are convert in words. Like Rs.20 =Twenty only( In Formula) up to 15 digit. Thanks Regards Hariom

Excel formula [ 1 Answers ]

I have more questions about excel formula. In one column I have data appearing, as either a 1 or 2, at random as I input it. Each column has around 120 rows. In the row at the bottom I created a formula to calculate how often, on average, the data appears in that column. 1st how do I get each row...

Excel formula [ 3 Answers ]

I am using Excel 2007. I have 2 Sheets. In first sheet there are 3 coloumns, first one contains the employes number, second coloumn contains the name of employes and third one containes the department he works in. In the second sheet I have drop down list of the names of employes and the...

Excel Formula [ 1 Answers ]

Hi I have come across another problem with my spreadsheet. I have tried quite a few different ways to do it but can't get any of them to work. Within my spreadsheet I have a quote price and a forcasted date. However when a order is definitely received then a sale price and a new date is put into...