troubleshooting Question

# Excel Conditional Summation Using SUMIFS

Experts...

Please look at the attached worksheet. For additional understanding of how the sheet works, several of the cells include comments as denoted by the red triangles in the upper right corner of the commented cells. All my calculations and formulas are working properly across the sheet except for the SUMIFS formulas in cells CZ4 and DA4. It is these two cells where the formulas need to be fixed.

The sheet includes weekly buckets for 12 weeks. Weeks 1 through 4, Weeks 6 through 9, and Weeks 11 and 12 are identically structured (same number of spaces between like columns). Weeks 5 and 10 are different in that they have a higher number of columns in those two weeks than do the others, but it is important to note that Weeks 5 and 10 are otherwise identical. Hence, a single SUMIFS will not work as one formula across the entire sheet as the referenced columns are not equidistant from each other week to week across all 12 weeks.  Therefore, the formulas in cells CZ4 and DA4 include nested formulas to deal with Weeks 5 and 10 differently, and still give the correct summations in cells CZ4 and DA4 for the full 12 weeks. There are SUMIFS formula segments for Weeks 1 through 4, Weeks 6 through 9, and Weeks 11 through 12, with IF() formulas imbedded to address Weeks 5 and 10, with each of these segments added together with a + between them.

The sheet is designed to hide everything within a given week (all calculated cells results, conditional formatting, etc.) until the 'X' is entered to activate the week for scoring. The sheet is currently active for Weeks 1 through 3 (with an 'X' in the shaded cell at the top left corner cell for each week) For testing purposes, you would need to activate subsequent weeks and then enter test data into those weeks to check your revised formulas in cells CZ4 and DA4 to see if they sum properly.

The SUMIFS formula in cell CZ4 is working fine and gives the correct total for Weeks 1 through 3, with the correct total of 243 in cell CZ4 through Week 3. However, the SUMIFS formula in cell DA4 is not working as it fails to pick up the total in cell AA35 for Week 3, only adding the numbers in cells K35 and S35 for a total of 91 instead of the correct total of 124. I modeled the formula in cell DA4 after the one in cell CZ4 and I cannot figure out why the formula in cell CZ4 works and the formula in cell DA4 is not working. What needs to be changed in the formula in cell DA4 to get it to pick up the total in cell AA35 and add it to the totals in cells K35 and S35? I have looked at the sheet and everything seems to be equidistant between the boundaries of the three SUMIFS segments in the formula, so it must be something else that is causing the formula not to pick up the number in cell AA35 and add it to the other two. Please place test data in Weeks 4 through 12 to ensure that the formula you construct for CZ4 and DA4 are summing correctly across all weeks.

Secondly, with regard to the full formulas in cells CZ4 and DA4, it is likely that these formulas are not constructed as efficiently as they could be to accommodate the different number of columns in Weeks 5 and 10. How would you modify them to be more efficient (perhaps by using another SUMIFS formula to deal with Weeks 5 and 10 rather than the IF() statements in the formulas for Weeks 5 and 10)?

Richard Daneke
Trainer
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.