Summary by name and date range

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

Find more posts tagged with

Manufacturing

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…