?
Solved

Conditional Summation in Excel

Posted on 2014-04-13
3
Medium Priority
?
357 Views
Last Modified: 2014-04-13
I need help building a conditional summation formula.

I want to sum the values in cells J36, R36, Z36, AH36, AP36, AR36, AY36, BG36, BO36, BW36, CE36, CG36, CN36 and CV36 and put the sum in Cell CZ4      and

to sum the values in cells L35, T35, AB35, AJ35, AS35, BA35, BI35, BQ35, BY35, CH35, CP35, and CX35 put the sum in Cell DA4

for only those weeks where the activation 'X' appears in the shaded cells in row 2 for the week.

That is, I am trying to get the sums calculated for cells CZ4 and DA4 for just those weeks where the activation 'X' appears in the shaded cells for the weeks in Row 2. I do not want the summation formulas in CZ4 and DA4 to consider any weeks other than those which are thus activated.

The simple sum formulas in CZ4 and DA4 are, I assume, returning #VALUE because there are no numbers (only blanks formatted as "") in rows 35 and 36 for those weeks not yet activated. (The cell formatting in the weekly totals footers is designed to hide these weekly totals cells until the week is activated in Row 2.) For the unactivated weeks, the totals cells at the bottom of the weekly columns are formatted to show blanks ("") until the week is activated, and, I assume, the simple summation formulas will not add the "" cells since there is no number in them, and is returning #VALUE in CZ4 and DA4 because of this. I assume the attached sheet uploaded with formulas intact so you can see the weekly totals formulas as well as how they respond in activated weeks and non-activated weeks.

In the attached example, only weeks 1 and 2 are activated, so the summations in CZ4 and DA4 should be for only those two weeks, ignoring weeks 3 through 12. As each successive week is activated, then the summations in CZ4 and DA4 should sum considering the designated totals cells in rows 35 and 36 for those weeks as they are activated across time.

I have tried constructing the SUMIFS formula to handle this, but I cannot get it to work. However, I am not even sure if this is the right formula to use in this case. I could write a very long nested IF statement (perhaps too long for the maximum formula length) to do this, but there should be a more straight-forward way to do it using a conditional summation formula in Excel. I just do not know which one to use nor how to build it to meet the required criteria.

What should the formulas in cells CZ4 and DA4 be in order to get summations for only the activated weeks' totals?
Class-Attendance-and-Participati.xlsx
0
Comment
Question by:Glenn Stearns
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39997635
Formula for cell CZ4:
=SUMPRODUCT((E2:CQ2="X")*(J3:CV3=$J$3),J36:CV36)

Formula for cell DA4:
=SUMPRODUCT((E2:CQ2="X")*(L3:CX3=$L$3),L35:CX35)
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39997661
You could also use SUMIFS:
=SUMIFS(J36:CV36,E2:CQ2,"X",J3:CV3,$J$3)
=SUMIFS(L35:CX35,E2:CQ2,"X",L3:CX3,$L$3)

By way of explanation, the suggested formulas are using offset ranges of the same length (E:CQ is the same number of columns as J:CV). The formulas are testing for the "X" in row 2 and the desired header label (Discussion Responses 5/5 or Instructor Posts) in row 3. When both those match, then add the values in row 35 or 36.

Because I am using a comma before the range to be summed (J36:CV36 or L35:CX35) in the SUMPRODUCT formulas previously suggested, any text in those columns will be ignored. That takes care of your problem with the blanks ("") in the cells corresponding to future weeks.
Class-Attendance-and-Participati.xlsx
0
 

Author Closing Comment

by:Glenn Stearns
ID: 39997677
Thanks for a great and prompt response! It worked just right!
0

Featured Post

Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question