Forum Discussion
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(S...
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)
=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)
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.