Current Nested IF statement to populate a Month by Year Grid on an MS Excel 2016 worksheet with the count of specific records that meet multiple critieria;
=IF($D$6="All_Ranges",SUM(COUNTIFS([Zone],$D$4, [Grade_Range],{"< 20","20 - 29","30 -39","40-49","50-59","60-69" ,"70-79","80-89",">90"},[Month],$C10, [Year],G$9 )), IF($D$6="<40",SUM(COUNTIFS([Zone],$D$4, [Grade_Range],{"< 20","20 - 29","30 -39"},[Month],$C10, [Year],G$9 )),IF(COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9)=0,NA(),COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9))))
Context:
Have an MS Excel 2016 workbook with the following worksheets; Data, Charts
On the worksheet "Charts", I have a line chart that is dynamically populated upon the selection of the dropdowns in the following cells;
D4: Can select various zones
D6: Can select GRADE RANGE - "All_Ranges", "<40", "< 20","20 - 29","30 -39","40-49","50-59","60-69" ,"70-79","80-89",">90"
Objective:
Using the nested if statement above, I need to modify so that regardless of what I select in cell D6 (All_Ranges or <40 or <20 or 20-29, etc.) either the result greater than 0 is displayed or #N/A is displayed if the result equals 0 (if there are no data for the month) or #N/A is displayed if there is an error so that the line chart does not drop to zero.
Note, I did modify the second argument to be IF(COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9)=0,NA(),COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9)
This appears to work for the most part but there are instances where if I select ">90" or "<40" in the drop down validation in cell D6, the result will be displayed as 0 not #N/A!
Did try to modify the other arguments but have not been able to meet the objective.
What modifications to the nested if statement above are needed so that either the result greater than 0 or N/A is always displayed?
Have tried multiple iterations but have not been successful.
Thinking that a combination IFERROR AND A CHECK TO DETERMINE IF THE RESULT IS A ZERO NEEDS TO BE USED FOR EACH ARGUMENT OF THE NESTED IF STATEMENT.
Thanks in advance for any insight!
=IF($D$6="All_Ranges",SUM(COUNTIFS([Zone],$D$4, [Grade_Range],{"< 20","20 - 29","30 -39","40-49","50-59","60-69" ,"70-79","80-89",">90"},[Month],$C10, [Year],G$9 )), IF($D$6="<40",SUM(COUNTIFS([Zone],$D$4, [Grade_Range],{"< 20","20 - 29","30 -39"},[Month],$C10, [Year],G$9 )),IF(COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9)=0,NA(),COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9))))
Context:
Have an MS Excel 2016 workbook with the following worksheets; Data, Charts
On the worksheet "Charts", I have a line chart that is dynamically populated upon the selection of the dropdowns in the following cells;
D4: Can select various zones
D6: Can select GRADE RANGE - "All_Ranges", "<40", "< 20","20 - 29","30 -39","40-49","50-59","60-69" ,"70-79","80-89",">90"
Objective:
Using the nested if statement above, I need to modify so that regardless of what I select in cell D6 (All_Ranges or <40 or <20 or 20-29, etc.) either the result greater than 0 is displayed or #N/A is displayed if the result equals 0 (if there are no data for the month) or #N/A is displayed if there is an error so that the line chart does not drop to zero.
Note, I did modify the second argument to be IF(COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9)=0,NA(),COUNTIFS([Zone],$D$4, [Grade_Range],$D$6,[Month],$C10, [Year],G$9)
This appears to work for the most part but there are instances where if I select ">90" or "<40" in the drop down validation in cell D6, the result will be displayed as 0 not #N/A!
Did try to modify the other arguments but have not been able to meet the objective.
What modifications to the nested if statement above are needed so that either the result greater than 0 or N/A is always displayed?
Have tried multiple iterations but have not been successful.
Thinking that a combination IFERROR AND A CHECK TO DETERMINE IF THE RESULT IS A ZERO NEEDS TO BE USED FOR EACH ARGUMENT OF THE NESTED IF STATEMENT.
Thanks in advance for any insight!