Summarizing and reporting data from several sheet columns

Joni S
Joni S ✭✭
edited 07/29/21 in Formulas and Functions

Hey all, I'm having trouble figuring out how to summarize data for a specific time period, for a specific customers. I have a date column, a customer plant column and total defects column. My Liaisons add data to the sheet when defects are found at the customer plant for which they cover. So I have several line items for each customer spanning months.

I want to sum the total number of defects for each customer for a specific time period, say each month. I am having trouble trying to figure out how to do this.

Here's an example of what my sheet looks like


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Joni S

    You can use a SUMIFS formula for this!

    If you set up a metrics sheet with each Customer Plant listed in a grid, then you can reference the name of the Customer Plant in your formula and write one formula for each line, like so:

    In my example, I have a separate column for the MONTH number I'm looking for. Then I can use this in my formula to check the MONTH in the cell of the other sheet and compare it to this number, like so:

    MONTH(@cell) = Month@row

    However the MONTH function can error when it looks through blank cells, so I'll wrap an IFERROR around it:

    IFERROR(MONTH(@cell), 0) = Month@row

    That's now my criteria for the SUMIFS!

    Full Formula:

    =SUMIFS({Total Defects Found}, {Customer Plant}, [Customer Plant]@row, {Created Date}, IFERROR(MONTH(@cell), 0) = Month@row)

    This means you can drag-fill it down your Metrics sheet and it will auto-update with numbers based on the Month you specify in the Month column and the Customer Plant. Is this what you were looking to do?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!