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


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.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

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

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

  39. PABLO says:

    My suggestion:

    1) Make a Table, just call it mydata.
    2) Then Make your stop list, and call it StopList
    3) Use a table-formula:
    =SUM(Hours)-DSUM(myData;C1;StopList)

    🙂

  40. Alex says:

    Sum of Hours for all activities excluding those in stop list?
    {=SUM(IF(NOT(COUNTIF(E2:E8,A2:A16)),C2:C16))}
    Sum of all Regular hours for activities not in stop list?
    {=SUM(IF(NOT(COUNTIF(E2:E8,A2:A16))*(B2:B16="Regular"),C2:C16))}

  41. Gayane says:

    Sum of Hours excluding stop list
    {=SUM(D3:D17)-SUM(IF((TRANSPOSE(B3:B17)=F4:F10)*1,TRANSPOSE(D3:D17),""))}

    Sum of Regular Hours excluding stop list
    {=SUM(D3:D17*(C3:C17="Regular"))-SUM(IF((TRANSPOSE(B3:B17)=F4:F10)*(TRANSPOSE(C3:C17)="Regular"),TRANSPOSE(D3:D17),""))}

Leave a Reply


« »