Multiple nested IF statements

[cduval]

Hi, I'm trying to change the IF statements in cells H5 through H10 as well as the same formulas (targeting the different name) in column J by using IF and then COUNTIF. The IF statements need to account for all changes in the dropdowns in E6, E7 and E8. For example, if I wanted to see how a player was doing vs a specific player for a specific tournament, it would populate just those numbers without taking into account the year if the year dropdown is "all". What is the best way to do these formulas?
https://www.mediafire.com/file/0pefl9om4cdn8xv/Tennis.xlsx

 

[Declamatory]

Re: complicated multiple nested IF statements

Can't open your file I'm afraid.

 

[cduval]

Re: complicated multiple nested IF statements

[TABLE="width: 500"]
<tbody>[TR]
[TD]player1[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$1&" Serving",'Raw Data'!$T:$T,"metric1",'Raw Data'!$G:$G,$a$3&" returning")[/TD]
[TD]metric 1[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$3&" Serving",'Raw Data'!$T:$T,"metric1",'Raw Data'!$G:$G,$a$1&" returning")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$1&" Serving",'Raw Data'!$T:$T,"metric2",'Raw Data'!$G:$G,$a$3&" returning")[/TD]
[TD]metric 2[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$3&" Serving",'Raw Data'!$T:$T,"metric2",'Raw Data'!$G:$G,$a$1&" returning")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]player2[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$1&" Serving",'Raw Data'!$J:$J,"metric3",'Raw Data'!$G:$G,$a$3&" returning")/COUNTIF('Raw Data'!$F:$F,$a$1&" serving")[/TD]
[TD]metric 3[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$3&" Serving",'Raw Data'!$J:$J,"metric3",'Raw Data'!$G:$G,$a$1&" returning")/COUNTIF('Raw Data'!$F:$F,$a$3&" serving")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]event[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$1&" Serving",'Raw Data'!$J:$J,"metric4",'Raw Data'!$G:$G,$a$3&" returning")/COUNTIF('Raw Data'!$F:$F,$a$1&" serving")[/TD]
[TD]metric 4[/TD]
[TD]=COUNTIFS('Raw Data'!$F:$F,$a$3&" Serving",'Raw Data'!$J:$J,"metric4",'Raw Data'!$G:$G,$a$1&" returning")/COUNTIF('Raw Data'!$F:$F,$a$3&" serving")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]year[/TD]
[TD]=COUNTIFS('Raw Data'!$G:$G,$a$1&" returning",'Raw Data'!$U:$U,$a$1&" metric5",'Raw Data'!$F:$F,$a$3&" serving")[/TD]
[TD]metric 5[/TD]
[TD]=COUNTIFS('Raw Data'!$G:$G,$a$3&" returning",'Raw Data'!$U:$U,$a$3&" metric5",'Raw Data'!$F:$F,$a$1&" serving")[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

instead of the dropdowns being in E6, E7 and E8, they are now in column A. I need a formula that will account for all combinations possible.

 

[istiasztalos]

Re: complicated multiple nested IF statements

Hi cduval,

Please explain more how your data is structured,where are these formulas? what is in your data columns? (anything references in your formulas) and most importantly, what calculation are you trying to achieve, as in, what are you trying to count with these countifs?

 

[cduval]

Re: complicated multiple nested IF statements

Hi, I'm running COUNTIF formulas off of a raw data tab. I'm just counting instances of specific variables (aces, double faults) where one player played another, but need to add in the variables of time and tournament when the dropdowns are changed on the summary page.
https://docs.google.com/spreadsheets/d/1Rlgi8CWgeudcRxoHr8gSFDMA61AJsNuMY6aaC2pvRu0/edit?usp=sharing

 

[istiasztalos]

Re: complicated multiple nested IF statements

Hi,

Did you solve this in the meantime? I see your criteria lists include now 'Raw Data'!$AJ:$AJ,$D$7,'Raw Data'!$AK:$AK,$D$8 which is to check Year and Event name

Hi, I'm running COUNTIF formulas off of a raw data tab. I'm just counting instances of specific variables (aces, double faults) where one player played another, but need to add in the variables of time and tournament when the dropdowns are changed on the summary page.
https://docs.google.com/spreadsheets/d/1Rlgi8CWgeudcRxoHr8gSFDMA61AJsNuMY6aaC2pvRu0/edit?usp=sharing

 

[cduval]

Re: complicated multiple nested IF statements

I did not solve this, but I wanted to be proactive and build in event and year