Counting Mutiple Criteria Help

[hammertime44]

I've built a golf stat tracker for myself. I figured out percentages of greens hit from certain distance to get a percentage.
This is it -
=SUMPRODUCT(($C$1:$C$11130>99)*($C$1:$C$11130<=124)*($A$1:$A$11130="hit"))/SUMPRODUCT(($C$1:$C$55555>99)*($C$1:$C$55555<=124))

However I have another column I need to factor in and cannot figure it out. I won't say the above is perfect but that works.

The next column, in C, has a number in it. 3, 4 or 5. That is the par. So I want to calculate the percentage of greens hit on Par 3 from specific distances, like above. The above are ALL function is for all holes, not specified by Par 3.

Here are a few things I tried but am not getting the correct number:
=SUMPRODUCT(($C$1:$C$150>124)*($C$1:$C$150<=150)*($A$1:$A$150="hit")*($B$1:$B$150="3"))/SUMPRODUCT(($C$1:$C$55555>124)*($C$1:$C$55555<=150))
or
=COUNTIFS($A$1:$A$150,"hit",$B$1:$B$150,"3")/SUMPRODUCT(($C$1:$C$150>124)*($C$1:$C$150<=150))

Thank you for help.

 

[FDibbins]

have you considered using averageifS()?

=averageifS(C:C,C:C,">99",C:C,"<=124",A:A,"hit")

 

[DRSteele]

Hi. Welcome to the forum.

At first glance, I think your ="3" should be =3

You could also use function AVERAGEIFS instead of the bulkier and costlier SUMPRODUCT.

 

[hammertime44]

DRSteele I tried =3 and doesn't work.
This works however - =COUNTIFS($A$1:$A$33330,"hit",$B$1:$B$33330,"3") for another function. So the "3" does work.
I will try the averageifs statement FDibbins and you recommend. Though I'm not trying to get an average of a group of numbers.

 

[hammertime44]

No luck with this -
=averageifS($C$1:$C$150,">124",$C$1:$C$150,"<=150",$A$1:$A$150,"hit")
plus I need column B too. Column B is only poplulated with 3, 4 or 5. I need "hit" in column A, 3 in column B and then set a range for column C. Column C are random numbers from say 85-220.
What is the percentage of Par 3's (Column B) that I hit the green from 125-149 yards? Would need to divide total attempts by total hit.

 

[Aladin Akyurek]

AverageIfs requires a range to average. Yours seems to be missing that....

 

[FDibbins]

you will notice that I repeated C:C in my formula, as the 1st argument...
=averageifS(C:C,C:C,">99",C:C,"<=124",A:A,"hit")

You can add additional criteria columns and criteria as needed

 

[DRSteele]

I might be missing something here, but if you are trying to produce a greens-hit-rate from a specific distance, you must include ALL of the data but use arrays math to apply specified criteria. So if you wish to find a greens-hit-rate on Par 3s from 124 yards to 150 yards, you need to calculate the number of such greens you hit and divide it be the number of such greens.

I think it should be this, where 1:55555 is all of the data: SUMPRODUCT(($C$1:$C$55555>124)*($C$1:$C$55555<=150)*($A$1:$A$55555="hit")*($B$1:$B$55555=3))/SUMPRODUCT(($C$1:$C$55555>124)*($C$1:$C$55555<=150)*($B$1:$B$55555=3))<strike></strike>

 

[hammertime44]

DRSteele,
That gives me a #DIV/0! error.
I think I got it. I needed to take a break. Spent about 3 hours on it and was going crazy.

This is working.
=SUMPRODUCT(($C$1:$C$533>150)*($C$1:$C$533<=174)*($A$1:$A$533="hit")*($B$1:$B$533=3))/SUMPRODUCT(($C$1:$C$533>150)*($C$1:$C$533<=174))
Also, I ended up changing column B to a number column. One of three functions I had wasn't working. When I changed it to <=175 I would get the right calculation but <=174 I was not for some odd reason. When I changed to all Numbers in column B, which is only a 3, 4 or 5 (for Pars), then it worked.

Thanks for the help and thanks above on averages. I'm going to mess with that a bit. I've never used those.

 

[Aladin Akyurek]

DRSteele,
That gives me a #DIV/0! error.
I think I got it. I needed to take a break. Spent about 3 hours on it and was going crazy.

This is working.
=SUMPRODUCT(($C$1:$C$533>150)*($C$1:$C$533<=174)*($A$1:$A$533="hit")*($B$1:$B$533=3))/SUMPRODUCT(($C$1:$C$533>150)*($C$1:$C$533<=174))
Also, I ended up changing column B to a number column. One of three functions I had wasn't working. When I changed it to <=175 I would get the right calculation but <=174 I was not for some odd reason. When I changed to all Numbers in column B, which is only a 3, 4 or 5 (for Pars), then it worked.

Thanks for the help and thanks above on averages. I'm going to mess with that a bit. I've never used those.

Why don't you switch to COUNTIFS, which should be faster?