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