Summary by name and date range

Jeremy_P

I will try to explain the best I can.  I don't have the Pivot app, so wondering if there are any other "tricks" that might work besides exporting to Excel.  We are using Smartsheet to log everything that goes through our final testing department.  So, assembler, qty inspected, qty rejected, date, etc.  Is there a way to summarize qty inspected and qty rejected for each assembler in a certain date range?  For example, for the month of October I want to see a summary like below.

Assembler    qty inspected     qty rejected

John              200                     10

Joe                300                      5

Paul Newcome

Can you provide a screenshot of your source sheet? Sensitive/confidential data can be hidden, removed, or replaced with "dummy data".

 

This can actually be done relatively easily. The exact solution though depends on how your data is currently being logged.

Jeremy_P

Uploaded screenshot

Paul Newcome

Ok. And I assume John could be listed multiple times going down the column?

 

Are you pulling this data onto another sheet or keeping it on the source sheet?

 

Do you want a sum of everything for John, or are you wanting to average?

Jeremy_P

Yes, John and other assemblers are listed multiple times. 

I am not currently pulling this data onto another sheet, but I can if it makes the process easier.

I would want the sum. 

Paul Newcome

Same sheet vs different sheet just changes how we use determine ranges is all. It makes no difference as to the solution itself.

 

Your basic formula is going to be a SUMIFS.

 

=SUMIFS([Column to Sum]:[Column to Sum], Assemblers:Assemblers, "John")

.

Use this as your base. Change the [Column to Sum] to the appropriate column you are wanting to sum and make sure that "John" is actually the text you are wanting to sum on. 

 

The formula above will only sum for those assemblers where John is the ONLY text within the cell. If last names are included, then you will want to include that inside of your quotes "John Doe".

 

According to your post, you have a column called Assembler (not to be confused with Assemblers in the SUMIFS). If that is the actual column name for the table you are housing this summary data in, you can use a cell reference to keep from having to manually enter every single different name.

.

=SUMIFS([Column to Sum]:[Column to Sum], Assemblers:Assemblers, Assembler@row)

Jeremy_P

Thanks.  It works on the same sheet.  However, I was attempting to use it on another sheet by using references and it gives an unparseable error.  See attachment for formula. 

QtyRec is Qty Received from "sheet2"

Assemble is Assemblers from "sheet2"

Assembler@ row is Assembler from the "local" sheet

Paul Newcome

If you are using the appropriate steps to create a cross sheet reference, you would only enter the range one time.

 

=SUMIFS({QtyRec}, {Assemble}, Assembler@row)

Jeremy_P

Awesome!  Thanks!

Paul Newcome

Happy to help! 

Jeremy_P

Next question.  Is it possible to add a date criterion as well?  The current formula works for summing everything for each assembler.  Can it also be summed by date as well?  I tried the below formula, but it is not working.

=SUMIFS({QtyRec}, {Assemble}, Assembler@row,{Date}>=DATE(2019,9,1),{Date}<DATE(2019,10,1))

Paul Newcome

Yes it can! You just missed adding commas between the ranges and criteria. Add in a comma after each of your {Date} references to separate them from their criteria, and you should be good to go.

Jeremy_P

Thanks so much for all your help!

Paul Newcome

Happy to help!