SUMIFS Question

[ajc623]

I am trying to create a dashboard where I can pull up info on a specific customer over the past 6 years and have it summarized for reference during check ins. I have everything I want right now except for pulling monthly sales data. Here is what I am trying to get

K3:K14 would be Jan:Dec
L2:Q2 would be 2011:2016
L3:Q14 would be the 72 months of sales data

They would also be only looking for sales from a specific customer which would be changed in A1

The data is being pulled from a complete list of sales on sheet 'Sales Data' with column B having the date, E is the customer name, and I being the sales number to sum. I have been able to pull the other info I want using SUMIFS and would like to do this the same way so the user only has to change the customer name in A1. If there is not an easy way to do this can I can look at using a pivot table. THanks

Andrew

 

[Jerry Sullivan]

Andrew, I think a PivotTable would be the easiest and best method.

If you prefer to use a formula, I'd suggest adding some fields to your dataset on Sheet Sales Data to parse out the Year and Month from the dates in Column B.
For the month, use month numbers on both Sales Data and on your Dashboard, so your SUMIFS formula doesn't have to handle conversions (e.g. "March" to 3).

A SUMIF or SUMPRODUCT formula could be constructed to work without using the helper columns, but that would be less efficient.

Edit: On your Dashboard, the cells with the Month Numbers would be formatted "Mmm" so they have the appearance you described.