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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
have you considered using averageifS()?

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

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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:

hammertime44

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

New Member
Joined
Mar 23, 2016
Messages
4

ADVERTISEMENT

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.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

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:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

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
Top