Simplifying Nested Countif with If Formula

[MHamid]

Hello,

I have a LOOOOOONG Countif nested formula . Had to double it with a + because I need to count businesses that are found in two different columns (AA_AP_Hierarchy_String_2 and a combination of AA_AP_L2_Business and AA_AP_Business_Impacted). Would anyone have any ideas how I could possibly simplify this nested formula?

=IF($DW$4<>"",IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B16,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Region_Revised,$DW$4,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed",AA_AP_Monthly_BUR_Date,"<="&$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B16,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Region_Revised,$DW$4,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed")),IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B16,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Audit_Status,"Completed",AA_AP_Monthly_BUR_Date,"<="&$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B16,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed")))+IF($DW$4<>"",IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),COUNTIFS(AA_AP_L2_Business,"Multi O&T Businesses",AA_AP_Business_Impacted,"*GCB Technology*",AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Region_Revised,$DW$4,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed",AA_AP_Monthly_BUR_Date,"<="&$F$2),COUNTIFS(AA_AP_L2_Business,"Multi O&T Businesses",AA_AP_Business_Impacted,"*GCB Technology*",AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Region_Revised,$DW$4,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed")),IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),COUNTIFS(AA_AP_L2_Business,"Multi O&T Businesses",AA_AP_Business_Impacted,"*GCB Technology*",AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Audit_Status,"Completed",AA_AP_Monthly_BUR_Date,"<="&$F$2),COUNTIFS(AA_AP_L2_Business,"Multi O&T Businesses",AA_AP_Business_Impacted,"*GCB Technology*",AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed")))

Thank you,

 

[KRice]

If I follow the logic correctly, this might work:

=SUMPRODUCT(
(AA_AP_Hierarchy_String_2 = $B16) + (AA_AP_L2_Business = "Multi O&T Businesses") + (ISNUMBER(SEARCH("GCB Technology",AA_AP_Business_Impacted))),
--(AA_AP_Report_Flag = "Yes"),
--(AA_AP_Control_Rating = $B$2),
--(IF($DW$4<>"", (AA_AP_Region_Revised = $DW$4) * (AA_AP_Published_Month <= $D$4), --(AA_AP_Published_Month <= 999999))),
--(AA_AP_Audit_Status = "Completed"),
--(AA_AP_Monthly_BUR_Date <= IF(COUNTIF(AA_DropDowns_EOMonth,$F$2), $F$2, 999999))
)

 

[KRice]

After playing around with the suggestion in my previous post, I think the first term in the SUMPRODUCT may need to be wrapped with a condition to avoid double counting...try:

=SUMPRODUCT(
--( ( (AA_AP_Hierarchy_String_2 = $B16) + (AA_AP_L2_Business = "Multi O&T Businesses") + (ISNUMBER (SEARCH("GCB Technology",AA_AP_Business_Impacted) ) ) )>=1),
--(AA_AP_Report_Flag = "Yes"),
--(AA_AP_Control_Rating = $B$2),
--(IF($DW$4<>"", (AA_AP_Region_Revised = $DW$4) * (AA_AP_Published_Month <= $D$4), --(AA_AP_Published_Month <= 999999))),
--(AA_AP_Audit_Status = "Completed"),
--(AA_AP_Monthly_BUR_Date <= IF(COUNTIF(AA_DropDowns_EOMonth,$F$2), $F$2, 999999))
)

...Or was the intent to double count rows involving AA_AP_Hierarchy_String_2, AA_AP_L2_Business, and AA_AP_Business_Impacted if two or more of them evaluate to TRUE on the same row (if that is possible)?

 

[KRice]

One more try...I made a mistake treating two of the criteria as OR's rather than AND's...the + sign should be multiplication sign and then those terms are wrapped with parentheses:

=SUMPRODUCT(
--(((AA_AP_Hierarchy_String_2=$B16)+((AA_AP_L2_Business="Multi O&T Businesses")*(ISNUMBER(SEARCH("GCB Technology",AA_AP_Business_Impacted)))))>=1),
--(AA_AP_Report_Flag = "Yes"),
--(AA_AP_Control_Rating = $B$2),
--(IF($DW$4<>"", (AA_AP_Region_Revised = $DW$4) * (AA_AP_Published_Month <= $D$4), --(AA_AP_Published_Month <= 999999))),
--(AA_AP_Audit_Status = "Completed"),
--(AA_AP_Monthly_BUR_Date <= IF(COUNTIF(AA_DropDowns_EOMonth,$F$2), $F$2, 999999)))

 

[KRice]

Upon further investigation, I think there is one more issue with how the criteria are applied to the AA_AP_Published_Month array. This version addresses that issue. I've rearranged the formula and grouped the easier to follow arrays together on the first line. The primary change introduced with this version is found on the last line, where an IF statement as been added to specify which criteria to use (999999 or $D$4).

=SUMPRODUCT(--(AA_AP_Report_Flag = "Yes"), --(AA_AP_Control_Rating = $B$2), --(AA_AP_Audit_Status = "Completed"),
--(((AA_AP_Hierarchy_String_2=$B16)+((AA_AP_L2_Business="Multi O&T Businesses")*ISNUMBER(SEARCH("GCB Technology",AA_AP_Business_Impacted))))>=1),
--(AA_AP_Monthly_BUR_Date <= IF(COUNTIF(AA_DropDowns_EOMonth,$F$2), $F$2, 999999)),
--(IF($DW$4<>"", (AA_AP_Region_Revised = $DW$4) * (AA_AP_Published_Month <= $D$4), --(AA_AP_Published_Month <=IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),999999,$D$4)))))

 

[MHamid]

Thank you so much. I will try it out today.

I actually had to add 1 more criteria to my original formula "AA_AP_Sector,"EIO&T". Where would i be able to plug this into your last formula? Should I add it in the beginning of the formula?

=IF($DW$4<>"",IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B12,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Region_Revised,$DW$4,AA_AP_Sector,"EIO&T",AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed",AA_AP_Monthly_BUR_Date,"<="&$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B12,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Region_Revised,$DW$4,AA_AP_Sector,"EIO&T",AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed")),IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B12,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Audit_Status,"Completed",AA_AP_Monthly_BUR_Date,"<="&$F$2),COUNTIFS(AA_AP_Hierarchy_String_2,$B12,AA_AP_Report_Flag,"Yes",AA_AP_Control_Rating,$B$2,AA_AP_Published_Month,"<="&$D$4,AA_AP_Audit_Status,"Completed")))

Thank you.

 

[KRice]

For the condition, AA_AP_Sector="EIO&T", when should it be applied? Is it a criteria that you want to include everywhere we have the other two text string criteria (AA_AP_L2_Business="Multi O&T Businesses" and AA_AP_Business_Impacted="*GCB Technology*"), and no where else? If so, then this should work:

=SUMPRODUCT(--(AA_AP_Report_Flag = "Yes"), --(AA_AP_Control_Rating = $B$2), --(AA_AP_Audit_Status = "Completed"),
--(((AA_AP_Hierarchy_String_2=$B16)+((AA_AP_Sector="EIO&T")*(AA_AP_L2_Business="Multi O&T Businesses")*ISNUMBER(SEARCH("GCB Technology",AA_AP_Business_Impacted))))>=1),
--(AA_AP_Monthly_BUR_Date <= IF(COUNTIF(AA_DropDowns_EOMonth,$F$2), $F$2, 999999)),
--(IF($DW$4<>"", (AA_AP_Region_Revised = $DW$4) * (AA_AP_Published_Month <= $D$4), --(AA_AP_Published_Month <=IF(COUNTIF(AA_DropDowns_EOMonth,$F$2),999999,$D$4)))))

This part of the formula:
--(((AA_AP_Hierarchy_String_2=$B16)+((AA_AP_Sector="EIO&T")*(AA_AP_L2_Business="Multi O&T Businesses")*ISNUMBER(SEARCH("GCB Technology",AA_AP_Business_Impacted))))>=1)
considers any rows where either of these conditions are met:
1. AA_AP_Hierarchy_String_2=$B16
2. AA_AP_Sector="EIO&T" ...AND... AA_AP_L2_Business="Multi O&T Businesses" ...AND... AA_AP_Business_Impacted contains the text string "GCB Technology"
Then to avoid double counting (in case both conditions are TRUE on the same row), we perform a check on the array elements to see if they are at least 1, and if so, return the value to 1.