Summarizing and reporting data from several sheet columns

Joni S

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

Q-Loop.xlsx

Genevieve P.

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?

Cheers,

Genevieve