Sum column values based on another column then divide that sum by another column single value.

[rmattern]

I need to sum a column conditionally based on another column then divide that sum by another single value from another column.  Looking at the data in the screen clip, I need to sum the numbers in column B when column D is less than C and then divide by column A all per column E.   So, in this case, the numbers would look like this.  (225 + 5) /230 = 1.  If row 67 did not meet the case of the date in D being less than C then I would expect the answer would be 225 / 300 = 0.978.

 

[Songbird]

First you need to create an index column in order to compare rows.  Then you can create a custom column (in Edit Query) with something like the following formula:

if #"Added Index"{[Index]+1}[E] = [E] then
  if [D] < [C] then
    (#"Added Index"{[Index]+1}[B] + [B]) / [A]
  else
    [B] / [A]
else 
  ??

[Songbird]

You need to first add an index to your table so you can compare rows.

Then you could create a new custom column with a formula something like this:

if #"Added Index"{[Index]+1}[E] = [E] then
  if [D] < [C] then
    (#"Added Index"{[Index]+1}[B] + [B]) / [A]
  else
    [B] / [A]
else 
  ??

[rmattern]

Thanks but I think you need more information.  The uniqueness per row is based on column E and the column to the left of C.  Also there is not always just 2 rows per column E. The number of rows per column E could be upto 10. 

[rmattern]

I added an index column named ID and I am trying to write the code for the "Custom Column" but it errors out.

Error: The name 'ID" wasn't recognized.

= if #"ID"{[Index]+1}[Submittal Number] = [Submittal Number] and #"ID"{[Index]+1}[Submittal Pass] = [Submittal Pass] then
if [Actual Submission Date] <= [Due Date] then
(#"ID"{[Index]+1}[#"Deliverables ""Accept"] + [#"Deliverables ""Accept"]) / [Deliverables]
else
0
else [#"Deliverables ""Accept"] / [Deliverables]