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

*as shown below.*

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

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

Hi Chandoo,

Did you forget to attach the sample file?

There is no attachment MF.

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

Edit...

No array input is required for the first formula

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])"

for the first problem:

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

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

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.

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.

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

And with array entry:

=SUMIF(MyData[Type],"Regular",MyData[Hours])-SUM(SUMIFS(MyData[Hours],MyData[Type],"Regular",MyData[Activity],Stop.List))

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

Good point!

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

@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

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!

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"

=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

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

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

Hi All

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

Regards

Sreekhosh

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

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

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

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.

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.

@Vaibhav,

Try: =SUM(Hours)-SUMPRODUCT((Activity=TRANSPOSE(Stoplist))*Hours)

Micky

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

First one:

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

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

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

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

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

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

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.

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

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

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

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

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

f3:f17 contrain the main table

h2:h9 contrain the stop list and the header is activity

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"

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

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

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

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)

🙂

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

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