 # 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

Excel School made me great at work.
5/5

– Brenda

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

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. ### Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

## Related Tips

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