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

It looks like you are not using SumIFS as designed.   This is a method to filter records in a list and conditioning add them based on the pairs of filter.  For example, sum sales where region is east.   You probably just need to use a direct IF statement.

Secondly, your areas can be named to make this easier.   Highlight a range of cells, click in the Name Box (where you active cell reference always shows - to the left of the formula bar) and type a name (no spaces), then press enter.   There are other methods and named areas are shown in the Name Manager in the Defined Names ribbon group of the Formulas tab.

I may post an alternative shortly.
Richard Daneke

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

Richard...

Thanks for responding. Your approach will work fine - it just makes for lengthy formulas that do the same thing the SUMIFS formlas do.

I found the problem with the formula in Cell DA4. SUMIFS is looking for exact matches in the criteria segments - and it does mean exact/identical, as I found out. When looking at the sheet cells in Row 3, 'Instructor Posts', Just looking at those cells for each week, they appeared identical. However, I found I had some of them entered as 'Instructor Posts' (all on one line, adjusting the column width to force 'Posts' onto the second line) while others were entered as 'Instructor <ALT/ENTER> Posts' in order to force the word 'Posts' to appear on the second line. While both ways work to get the appearance I was after, Excel (SUMIFS) wants the structure to be all one way or all the other. When I corrected all of them to 'Instructor <ALT/ENTER> Posts', the SUMIFS summation in Cell DA4 corrected itself and now shows the correct sum.

I appreciate you providing an alternative and workable approach to the problem. Thanks so much for providing a great response!

glennes