What is the sum of values excluding items on stop list? [home work]

Posted on June 10th, 2016 in Excel Challenges , Learn Excel - 45 comments

Okay, this is an extension of the Neither “A” Nor “B” sum problem we discussed few days back.

Imagine you have a table named mydata with a few columns and a stop list named stop.list as shown below.

sum-excluding-stop-list

How would you calculate,

  • Sum of Hours for all activities excluding those in stop list?
  • Sum of all Regular hours for activities not in stop list?

So go ahead and post your answers in the comments.

I am waiting…

Want more challenges?

Attempt below home work problems as well.

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

45 Responses to “What is the sum of values excluding items on stop list? [home work]”

  1. MF says:

    Hi Chandoo,
    Did you forget to attach the sample file?

  2. MF says:

    For sum of hours excluding stop list
    =SUM(mydata[Hours])-SUMPRODUCT(LOOKUP(stop.list[Stop List],mydata))
    Ctrl+Shift+Enter

    For sum of Regular hours excluding stop list
    =SUMIF(mydata[Type],"Regular",mydata[Hours])-SUM(--(mydata[Activity]=TRANSPOSE(stop.list[Stop List]))*mydata[Hours]*--(mydata[Type]="Regular"))
    Ctrl+Shift+Enter

    btw, I would definitely use Pivot Table to solve problem like this. 🙂

  3. Gis a job in reading berks says:

    103, 87
    No, I couldn't do it in one formula. I had to have a "helper" column, but I don't think it is desirable to do it in one go and risk making errors for the sake of showing off.

    Formula for helper column, which I headed "Hours-stop.list"
    " =(1-COUNTIF(stop.list,[@Activity]))*[@Hours]"

    Sum of Hours excluding stop.list
    "=SUM(mydata[Hours-stop.list])"

    Sum of Regular hours excluding stop.list
    "=SUMIF(mydata[Type],"Regular",mydata[Hours-stop.list])"

    Of course this could have been accomplished in an ad hoc way with by filtering with a helper column with the formula:
    "=COUNTIF(stop.list,[@Activity])"

    and the filtered hours added with the formula:
    "=SUBTOTAL(9,[Hours])"

  4. Vaibhav Garg says:

    for the first problem:
    =SUM($C$2:$C$16)-SUMPRODUCT(COUNTIFS(Stop.List,$A$2:$A$16),$C$2:$C$16)

  5. Gis a job in reading berks says:

    Sorry answers are 92 and 76. I typed a 22 instead of 11, so both answers were 11 higher than they should have been.

  6. Lewis Kirby says:

    First problem:
    =SUM(mydata[Hours]*(IFERROR(MATCH(mydata[Activity],stop.list[Stop List],0),0)>0))
    as an ARRAY FORMULA.
    Second problem:
    =SUM(mydata[Hours]*(IFERROR(MATCH(mydata[Activity],stop.list[Stop List],0),0)>0)*(mydata[Type]="Regular"))
    as an ARRAY FORMULA.

  7. Vaibhav Garg says:

    2nd problem, without array entry
    =SUMIF(MyData[Type],"Regular",MyData[Hours])-SUMPRODUCT(COUNTIFS(Stop.List,$A$2:$A$16),$C$2:$C$16,--(MyData[Type]="Regular"))

    A,B and C are the 3 columns of my data. I forgot to convert this to a table, hence the references being what they are.

    • susej says:

      I have my table with that references(A,B,C) and the firts part

      =SUMIF(MyData[Type],"Regular",MyData[Hours]) result 0

      but I change "regular" for "one off" similar to
      =SUMIF(MyData[Type],"one off",MyData[Hours]) result 51

      I dont know for what reason this happen but I will lost my mind for that. please help

  8. Vaibhav Garg says:

    And with array entry:
    =SUMIF(MyData[Type],"Regular",MyData[Hours])-SUM(SUMIFS(MyData[Hours],MyData[Type],"Regular",MyData[Activity],Stop.List))

  9. Michael (Micky) Avidan says:

    As long as nobody stated that all Activities are uniqe values - I would suggest using:
    =SUM(C2:C16)-SUM(SUMIF(A2:A16,E2:E8,C2:C16))
    as an Array Formula (Ctrl+Shift+Enter)
    for summing the Hours for all activities excluding those in the stop list.
    Micky

  10. Brian says:

    For "Hours Excluding Stop":

    =SUM(IF(ISNUMBER(MATCH(Activity,Stop_List,0))=FALSE,Hours,0))

    For Regular Hours Excluding Stop:

    =SUM(IF(ISNUMBER(MATCH(Activity,Stop_List,0))=FALSE,IF(Type="Regular",Hours,0)))

    Both array formulas

    • Michael (Micky) Avidan says:

      @Brian,
      To my opinion, the first formula can be shorten to: =SUM(ISNA(MATCH(A2:A16,E2:E8,0))*C2:C16)
      and the second one to:
      =SUM(ISNA(MATCH(A2:A16,E2:E8,0))*(B2:B16="Regular")*C2:C16)
      Micky

  11. John Jairo V says:

    Hi to all!

    For first problem:
    =SUMPRODUCT(1-COUNTIF(E2:E8,A2:A16),C2:C16)

    For second problem:
    =SUMPRODUCT(1-COUNTIF(E2:E8,A2:A16),N(B2:B16="Regular"),C2:C16)

    Blessings!

  12. Ken Puls says:

    Hey Chandoo, 3 steps using Power Query:

    let
    Source = Table.NestedJoin(mydata,{"Activity"},#"stop list",{"Stop List"},"NewColumn",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"NewColumn", "Activity"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Hours", List.Sum)
    in
    #"Pivoted Column"

  13. Haz says:

    =SUMPRODUCT(mydata[Hours]*ISNA(MATCH(mydata[Activity],stop.list,0)))
    =SUMPRODUCT(mydata[Hours]*ISNA(MATCH(mydata[Activity],stop.list,0))*(mydata[Type]="Regular"))

    92 and 76

  14. Marcus says:

    Another sumproduct answer! No Shift-Control-Enter required!

    =SUMPRODUCT(--NOT(COUNTIF(stopList[Stop list],data[Activity])),data[Hours])

    =SUMPRODUCT(--NOT(COUNTIF(stopList[Stop list],data[Activity])),data[Hours],--(data[Type]="Regular"))

  15. Kuldeep says:

    Sum of hours excluding Stop list..

    =SUM(MyData[Hours])-SUM(IF(MyData[Activity]={"A","C","G","H","B","K","O"},MyData[Hours])) with CSE

    Sum of regular hour excluding stop list..

    =SUM((MyData[Type]="Regular")*(MyData[Hours]))-SUMPRODUCT(--(MyData[Type]="Regular")*(MyData[Activity]={"A","C","G","H","B","K","O"})*(MyData[Hours])) with CSE

  16. Sreekhosh says:

    Hi All

    {=SUM(Hours,-(SUMIF(Activity,(StopList),Hours)))}

    Regards
    Sreekhosh

  17. AYFIQ says:

    "Stop.List" name range for:
    Activity Activity Activity Activity Activity Activity Activity
    A C G H B K O

    "Regular.and.Stop.List" name range for:
    Type Activity Activity Activity Activity Activity Activity Activity
    =Regular A C G H B K O

    Formulas:
    Exclude StopList
    = DSUM(mydata[#All],mydata[[#Headers],[Hours]],Stop.List)

    Exclude StopList but Include Regular Hours
    =DSUM(mydata[#All],mydata[[#Headers],[Hours]],Regular.and.Stop.List)

  18. AYFIQ says:

    "Stop.List" name range for:
    Activity Activity Activity Activity Activity Activity Activity
    ="A" ="C" ="G" ="H" ="B" ="K" ="O"

    "Regular.and.Stop.List" name range for:
    Type Activity Activity Activity Activity Activity Activity Activity
    ="=Regular" ="A" ="C" ="G" ="H" ="B" ="K" ="O"

    Formulas:
    Exclude StopList
    = DSUM(mydata[#All],mydata[[#Headers],[Hours]],Stop.List)

    Exclude StopList but Include Regular Hours
    =DSUM(mydata[#All],mydata[[#Headers],[Hours]],Regular.and.Stop.List)

  19. Luke M says:

    First formula (both types)

    =SUM(IF(COUNTIF(stop.list,mydata[Activity])=0,mydata[Hours]))

    Second (regular only)

    =SUM(IF((mydata[Type]="Regular")*(COUNTIF(stop.list,mydata[Activity])=0),mydata[Hours]))

  20. Vaibhav says:

    Hey Chandoo !! im getting the required answer thru

    =SUM(Hours)-SUMPRODUCT((Activity=Stoplist)*Hours)

    where Hours ,Activity , Stoplist are range references .

    However Im only getting the Answer when Im Keeping the Stoplist in a Horizontal Column and getting "#N/A" error when keeping it in a vertical column . Cant Make head and toe of this . could you pls comment on this Pleeez.

    • NARAYAN says:

      Hi Vaibhav ,

      That is the way the equality operator works ; if you have two lists of data , called List1 and List2 , then a simple formula such as :

      =List1 = List2

      results in an array of values which will be as long as the number of elements in the longer list.

      Thus , if any one of the lists is 7 elements long , and the other is say 3 elements long , the result array will be 7 elements long , with TRUE / FALSE values for the first three elements , and the #N/A error value for the remaining 4 elements.

      This is because the comparison is done between the first element of one list and the first element of the other list ; this comparison proceeds down the lists , and when ever one list ends , the remaining results are all the #N/A error values.

      When you do as suggested by Micky Avidan , and use a TRANSPOSE , you are converting one of the vertical lists to a horizontal list ; now , when you use the equality operator in the formula , as in :

      =List1 = TRANSPOSE(List2)

      Excel generates a matrix of TRUE / FALSE results , where one row corresponds to a comparison of one element of List1 with every element of List2 ; thus , there are as many columns as the number of elements in List2 , and as many rows as the number of elements in List1.

  21. Michael (Micky) Avidan says:

    @Vaibhav,
    Try: =SUM(Hours)-SUMPRODUCT((Activity=TRANSPOSE(Stoplist))*Hours)
    Micky

  22. mithun says:

    1. =+SUM((HOURS)*NOT((IFERROR(MATCH(ACTIVITY,STOPLIST,0),0)))) with answer 92

    2. =+SUM((HOURS)*NOT((IFERROR(MATCH(ACTIVITY,STOPLIST,0),0)))*(TYPE="Regular")) with answer 76

  23. Jason Morin says:

    First one:

    =SUMPRODUCT(IF(ISNA(MATCH(mydata[Activity],stop.list,0)),mydata[Hours]))

  24. Achuyth says:

    First one: =SUM(SUMIFS(C3:C17,A3:A17,{"d","e","f","i","j","l","m","n"}))

    Second one: =SUM(SUMIFS(C3:C17,A3:A17,{"d","e","f","i","j","l","m","n"},$B$3:$B$17,"regular"))

  25. Nitin says:

    =SUM(Stop.list)-DSUM(MyData,"Hours",Stop.list)

  26. Anthony says:

    First: =DSUM(A1:C16,"Hours",H2:N3)
    Second: =DSUM(A1:C16,"Hours",G2:N3)

    Using reference to the following table in cells G2 to N3:

    Type Activity Activity Activity Activity Activity Activity Activity
    Regular A C G H B K O

  27. Cary says:

    1. =SUMPRODUCT(--(ISNA(MATCH(mydata[Activity],stop,0))),mydata[Hours])

    2. =SUMPRODUCT(--(ISNA(MATCH(mydata[Activity],stop[Stop List],0)))*(mydata[Type]="Regular"),mydata[Hours])

  28. Reila says:

    Sum of Hours excluding stop list?
    =DSUM(mydata,Hours,stop.list)

    Sum of Regular Hours excluding stop list?
    =DSUM(mydata,Hours,stop.list.regular)

    Stop List
    Type Activity Activity Activity Activity Activity Activity Activity
    Regular A C G H B K O

  29. Amy says:

    1. =sumproduct((activitystop list)*(hours))
    2. =sumproduct((activitystop list)*(type="Regular")*(hours))

    I have named the titles instead of showing the arrays, SUMPRODUCT can work with an array in criteria a swell, hence why I list the stop list.

  30. Amit namdeo says:

    Sum of Hours excluding stop list?
    =DSUM(Mydata,"Hour",stop.list)

    Sum of Hours excluding stop list?
    =DSUM(Mydata,"Hour",stop.list.regular)

    stop.list
    Activity Activity Activity Activity Activity Activity Activity Activity Type
    A D F H I J L O Regular

  31. Amit namdeo says:

    Sum of Hours excluding stop list?
    =DSUM(Mydata,"Hour",stop.list)

    Sum of Hours excluding stop list?
    =DSUM(Mydata,"Hour",stop.list.regular)

    stop.list
    Activity Activity Activity Activity Activity Activity Activity Activity Type
    A D M H I J L O Regular

  32. Vijaykumar Shetye says:

    Dear Chandoo,

    Array Formula for Sum of Hours for all activities excluding those in stop list
    =SUMPRODUCT(IF(ISNUMBER(MATCH($A$2:$A$7,stop.list,0)),0,1),$C$2:$C$7)

    Array Formula for Sum of all Regular hours for activities not in stop list?
    =SUMPRODUCT(IF(ISNUMBER(MATCH($A$2:$A$7,stop.list,0)),0,1),IF($B$2:$B$7="regular",1,0),$C$2:$C$7)

    Vijaykumar Shetye, Goa, India

  33. Avinash Kedumulor says:

    Hi Chandoo,
    Sum of Hours excluding stop list?
    {=SUM(ISNA(MATCH(Activity,Stoplist,0))*Hours)}
    Sum of Hours excluding stop list?
    {=SUM(ISNA(MATCH(Activity,Stoplist,0))*Hours*(Type="Regular"))}

  34. wassal says:

    SUM(F3:F17)-DSUM(D2:F17,F2,H2:H9)

  35. Alberto says:

    First: =DSUM(A1:C16,"Hours",H2:N3)
    Second: =DSUM(A1:C16,"Hours",G2:N3)

    Using reference to the following table in cells G2 to N3:

    Type Activity Activity Activity Activity Activity Activity Activity
    ="=Regular" ="A" ="C" ="G" ="H" ="B" ="K" ="O"

  36. CC says:

    =SUMPRODUCT(1*NOT(COUNTIF(stop.list[Stop List],mydata[Activity])),mydata[Hours],1*(mydata[Type]="Regular"))

  37. Luís Pires says:

    Sum of Hours excluding stop list

    =SUM(mydata[Hours])-SUM(SUMIF(mydata[Activity];Stop.List;mydata[Hours])) ctrl+shift+enter

    Sum of Regular excluding stop list

    =SUMIF(mydata[Type];"Regular";mydata[Hours])-SUM(SUMIFS(mydata[Hours];mydata[Activity];Stop.List;mydata[Type];"Regular")) ctrl+shift+enter

  38. SAM says:

    =SUM(C3:C16)-SUMPRODUCT(LOOKUP(G2:G6,B3:B16,C3:C16))-SUMIF(I2:I6,I2,H2:H6)

Leave a Reply