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,
So go ahead and post your answers in the comments.
I am waiting…
Want more challenges?
Attempt below home work problems as well.
Share this tip with your colleagues
Simple, fun and useful emails, once per week.
Learn & be awesome.
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 story • FREE Excel tips book
From simple to complex, there is a formula for every occasion. Check out the list now.
Calendars, invoices, trackers and much more. All free, fun and fantastic.
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.
This week, let’s try something new. I have a spreadsheet challenge for you. Solve it, post your answers by leaving a comment. No cheating or
48 Responses
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),””))}