sum values in a range to get monthly totals by customer if values match a reference column

[rkobeyer]

Hi All,

I am trying to figure out a better way to get monthly totals for data imported from a Gantt chart into columns A to D.

I added a hidden reference column that gets imported to column A in Excel.

The table in the image below needs to show the monthly orders for:

• Australia which includes references ref1 + ref2 + ref3

• Customer 1 which includes ref2, and so on for the other customers...
• Then a total including all references from ref1 to ref6

I am currently using the formula below:
=SUMIFS(australia_orders,australia_dates,">="&G$2,australia_dates,"<="&EOMONTH(G$210))

But this involves manually selecting and naming ranges and with over 40 customers it takes a long time. Other people are working collaboratively on the Gantt chart and constantly updating and changing rows/columns. So the next time I import the data, I need to check and redo the ranges all over again.

It will take a fraction of the time if I can assign each row a reference in the Gantt chart, then get Excel to do the rest.

Any suggestions will be greatly appreciated.

Cheers,
Russell

 

[Michael M]

Have you tried using a PivotTable ??

 

[rkobeyer]

Have you tried using a PivotTable ??

I have never used PivotTables before so did not consider it, could you link me to any kind of guide using PivotTables that is close to what I am trying to do?

 

[Michael M]

Have a look at this link

https://www.contextures.com/CreatePivotTable.html