I have my data arranged as:
Name1, Name2, TotalCount (please see attached file). I need to populate Columns D thru G with following logic:
ColumD (Name1>100&Name2<25): if sum of totalcount over Name1> 100 and sum of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumD.
ColumE (Name1<100&Name2<25): if sum of totalcount over Name1< 100 and sum of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumE.
Note: This is the same as ColumnD with < 100
ColumF (Name1>100&Name2<25): if sum of totalcount over Name1> 100 and min of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumF.
good example are X7 records. Where sum of totalcoutn > 100 and only first record where Name2=Y15 and totalcount=6 takes the value of 1
ColumG (Name1>100&Name2<25): if sum of totalcount over Name1< 100 and min of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumG.
Note: This is the same as ColumnF with < 100
good example are X4 records where sum of totalcount< 100 but only the second record where Name2=Y6 and totalcount=7 take the value of 1
Hope this is clear as to what I need. I believe the first two columns D& E can be accomplished by:
=AND(C2 < 25, SUMIF($A$2:$A$19, A2, $C$2:$C$19) > 100)+0
=AND(C2 < 25, SUMIF($A$2:$A$19, A2, $C$2:$C$19) < 100)+0
Thanks for your help
Bookmarks