 # Microsoft Excel Formula Assistance

Hello,

Here is my existing formula. This logic checks the Column K (rows through 2-14) for "Rej"

=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$14=A2,IF(\$D\$2:\$D\$14="Final",IF(\$I\$2:\$I\$14="C D w/ I",IF(ISERROR(SEARCH("Rej",\$K\$2:\$K\$14)),\$E\$2:\$E\$14)))),--\$E\$2:\$E\$14),\$E\$2:\$E\$14))

How would I remove the logic to check for "Rej" without impacting the rest of this formula?

4 Replies

# Re: Microsoft Excel Formula Assistance

If you want to remove the check for "Rej", then I think this would be your formula:

=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$14=A2,IF(\$D\$2:\$D\$14="Final",IF(\$I\$2:\$I\$14="C D w/ I",\$E\$2:\$E\$14))),--\$E\$2:\$E\$14),\$E\$2:\$E\$14))

But, it appears you are doing a conditional sum? If you have the sumifs function, then you might try this, I think it would be easier to follow:

=SUMIFS(\$E\$2:\$E\$14,\$A\$2:\$A\$14,A2,\$D\$2:\$D\$14,"Final",\$I\$2:\$I\$14,"C D w/ I")

If, by chance, you don't have sumifs, then you could try this:

=SUMPRODUCT(--(\$A\$2:\$A\$14=A2),--(\$D\$2:\$D\$14="Final"),--(\$I\$2:\$I\$14="C D w/ I"),\$E\$2:\$E\$14)

# Re: Microsoft Excel Formula Assistance

Thanks JMB17
What I am trying to do is this (hopefully this will make sense):
If Column A has the same value, Column D = "Final" and Column I = "C D w/ I" then aggregate the values in Column E.
However, if the values in Column E have duplicate values, only aggregate one of these values. When Column E has different values, aggregate all of them when the other conditions are met. If no calculation can be performed, output a '-'.

I put a few examples below.

# Re: Microsoft Excel Formula Assistance

 Column A Column D Column E Column I Expected Value 111 Final 100 C D w/ I 150 111 Final 100 C D w/ I 150 111 Final 50 C D w/ I 150 111 Test 1000 Test 150 222 Final 500 Test - 333 Final 100 C D w/ I 100 333 Final 100 C D w/ I 100 444 Final 100 C D w/ I 250 444 Final 150 C D w/ I 250

# Re: Microsoft Excel Formula Assistance

It appears the first suggestion works, slightly modified to remove some of the IF statements (I included a second example in the workbook that I believe would also work, although longer) . You may have to hit Ctrl+Shift+Enter after copying/keying it into the formula bar instead of just enter (may not be necessary on the latest version of office 365).

=SUM(IF(FREQUENCY(IF((\$A\$2:\$A\$10=A2)*(\$D\$2:\$D\$10="Final")*(\$I\$2:\$I\$10="C D w/ I"),\$E\$2:\$E\$10),--\$E\$2:\$E\$10),\$E\$2:\$E\$10))

To get the "-" for zero values, I would suggest using a custom number format.