Counting Mutiple Criteria Help

hammertime44

New Member
Joined
Mar 23, 2016
Messages
4
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
have you considered using averageifS()?

=averageifS(C:C,C:C,">99",C:C,"<=124",A:A,"hit")
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top