Microsoft Excel Formula Assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-2157810%22%20slang%3D%22en-US%22%3EMicrosoft%20Excel%20Formula%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2157810%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20existing%20formula.%20This%20logic%20checks%20the%20Column%20K%20(rows%20through%202-14)%20for%20%22Rej%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(FREQUENCY(IF(%24A%242%3A%24A%2414%3DA2%2CIF(%24D%242%3A%24D%2414%3D%22Final%22%2CIF(%24I%242%3A%24I%2414%3D%22C%20D%20w%2F%20I%22%2CIF(ISERROR(%3CFONT%20color%3D%22%23FF0000%22%3E%3CSPAN%3ESEARCH(%22Rej%22%2C%24K%242%3A%24K%2414%3C%2FSPAN%3E%3C%2FFONT%3E))%2C%24E%242%3A%24E%2414))))%2C--%24E%242%3A%24E%2414)%2C%24E%242%3A%24E%2414))%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20would%20I%20remove%20the%20logic%20to%20check%20for%20%22Rej%22%20without%20impacting%20the%20rest%20of%20this%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2157810%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2157834%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20Formula%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2157834%22%20slang%3D%22en-US%22%3EIf%20you%20want%20to%20remove%20the%20check%20for%20%22Rej%22%2C%20then%20I%20think%20this%20would%20be%20your%20formula%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUM(IF(FREQUENCY(IF(%24A%242%3A%24A%2414%3DA2%2CIF(%24D%242%3A%24D%2414%3D%22Final%22%2CIF(%24I%242%3A%24I%2414%3D%22C%20D%20w%2F%20I%22%2C%24E%242%3A%24E%2414)))%2C--%24E%242%3A%24E%2414)%2C%24E%242%3A%24E%2414))%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20it%20appears%20you%20are%20doing%20a%20conditional%20sum%3F%20If%20you%20have%20the%20sumifs%20function%2C%20then%20you%20might%20try%20this%2C%20I%20think%20it%20would%20be%20easier%20to%20follow%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUMIFS(%24E%242%3A%24E%2414%2C%24A%242%3A%24A%2414%2CA2%2C%24D%242%3A%24D%2414%2C%22Final%22%2C%24I%242%3A%24I%2414%2C%22C%20D%20w%2F%20I%22)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%2C%20by%20chance%2C%20you%20don't%20have%20sumifs%2C%20then%20you%20could%20try%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUMPRODUCT(--(%24A%242%3A%24A%2414%3DA2)%2C--(%24D%242%3A%24D%2414%3D%22Final%22)%2C--(%24I%242%3A%24I%2414%3D%22C%20D%20w%2F%20I%22)%2C%24E%242%3A%24E%2414)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2157867%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20Formula%20Assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2157867%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3EJMB17%3C%2FA%3E%3CBR%20%2F%3EWhat%20I%20am%20trying%20to%20do%20is%20this%20(hopefully%20this%20will%20make%20sense)%3A%3CBR%20%2F%3EIf%20Column%20A%20has%20the%20same%20value%2C%20Column%20D%20%3D%20%22Final%22%20and%20Column%20I%20%3D%20%22C%20D%20w%2F%20I%22%20then%20aggregate%20the%20values%20in%20Column%20E.%3CBR%20%2F%3EHowever%2C%20if%20the%20values%20in%20Column%20E%20have%20duplicate%20values%2C%20only%20aggregate%20one%20of%20these%20values.%20When%20Column%20E%20has%20different%20values%2C%20aggregate%20all%20of%20them%20when%20the%20other%20conditions%20are%20met.%20If%20no%20calculation%20can%20be%20performed%2C%20output%20a%20'-'.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20put%20a%20few%20examples%20below.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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
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)

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.



 

Column AColumn DColumn EColumn IExpected Value
111Final100C D w/ I150
111Final100C D w/ I150
111Final50C D w/ I150
111Test1000Test150
222Final500Test

-

333Final100C D w/ I100
333Final100C D w/ I100
444Final100C D w/ I250
444Final150C D w/ I250

@cubd8 

@cubd8 

 

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.