Customer, value contracted, by month for Metrics

Odette Woods

Hi,

I have a grid that contains multiple columns, that include:

Customer

Value $

Date Contracted

I need a formula that will return all these results that can then be presented via a bar chart that shows the customer and value contracted by month, either stacked (best) or shows if you hover over the bar (acceptable) - but am failing to make this work. Any help would be hugely appreciated :)

KPH

You need a column that contains each customer name and a column for each month. The formula in the month cell will probably be a SUMIFS formula. The syntax for SUMIFS is

=SUMIFS(range,criterion_range1,criterion1,criterion_range2,​criterion2​)

• Your range will be the column containing the order value,
• criterion range 1 will be the column containing the Customer name,
• criterion 1 will be the customer name on the current row,
• criterion range 2 will be the column containing the date,
• criterion 2 will be the month for the particular column.

You might also need to include a criterion for year if your data covers more than the period you want to chart.

KPH

HI @Odette Woods

Is this the sort of thing you are looking for?

Stacked bar, that shows the monthly volume when hovered over.

To achieve this, you need to get your data into a format that matches the shape of the chart, like this:

You can do that using SUMIFS formula and the MONTH function. If you share a sample of your data (hiding company names), we can help with the formula to create this.

The settings to make the graph are:

Odette Woods

Hi,

That looks Fab - thank you so much!! I'm not sure I can share any of the data, but if you can share your formula you used, I will be able to modify it to my data layout and make it work!!

KPH

You need a column that contains each customer name and a column for each month. The formula in the month cell will probably be a SUMIFS formula. The syntax for SUMIFS is

=SUMIFS(range,criterion_range1,criterion1,criterion_range2,​criterion2​)

• Your range will be the column containing the order value,
• criterion range 1 will be the column containing the Customer name,
• criterion 1 will be the customer name on the current row,
• criterion range 2 will be the column containing the date,
• criterion 2 will be the month for the particular column.

You might also need to include a criterion for year if your data covers more than the period you want to chart.