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

Share

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. 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?

I am waiting…

Want more challenges?

Attempt below home work problems as well.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

I will be presenting at two events in London in April 2020. Come & join me.

Chandoo is an awesome teacher
5/5

– Jason

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### 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?

• Chandoo says:

There is no attachment MF.

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. 🙂

• MF says:

Edit...
No array input is required for the first formula

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

• MF says:

Good point!

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

Exclude StopList but Include Regular Hours

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

Exclude StopList but Include Regular Hours

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:

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)

• wassal says:

f3:f17 contrain the main table
h2:h9 contrain the stop list and the header is activity

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),""))}

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.