# Summary by name and date range

edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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?

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

• ✭✭✭✭✭✭

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)

• 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

• ✭✭✭✭✭✭

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)

• Awesome!  Thanks!

• ✭✭✭✭✭✭
• 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))

• ✭✭✭✭✭✭

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.

• Thanks so much for all your help!

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!