Sum cells in range based on matching/non unique identifiers

[hma87]

Hi there!
I'm trying to find the total subtotal generated by marketing. For some rows, this is easy, as I can just say that 7.38 in spend generated 31.95 in revenue (row 111), for example. However, with a certain type of ad the spend and revenue information is coming as two separate transactions. The 3.89 of marketing spend (row 113) for example, is responsible for the 31.95 in row 115, which we can be certain of because of their matching ID's in column M. I'm looking to sum all the order* subtotals where their ID is duplicated / associated with an ad fee. Because I'm trying the whole broader process into a macro later on, it would be much more useful if this could be done in one formula rather than identifying them with one, laying them out in a separate column, and then summing them. Thanks for your help!

 

[pjmorris]

This is probably not what you were expecting, but the easiest way is to use the Powerquery tools. I'm assuming that the data above is exported from an accounting tool.

On the in the 'Get & Transform' tab of the Data Ribbon in Excel, select 'From Table/Range'. Choose the 'Transform' option. This will open your source table in the Powerquery User Interface. On the 'Transform' ribbon you'll find 'Group By'. When the dialogue opens select the code column on which to group by, then click on the 'advanced' option to allow multiple aggregations. One option worth doing is to click on an aggregation operation and selection 'all rows', which will allow you to reopen all the rows should you wish. When you click Ok you'll get a grouped table with the columns summed. If you click on 'Close & Load' on the Home ribbon you new table will be created on a new sheet in your spreadsheet (other options are available). If your source data is updated only a Refresh is required to update the grouped data.

This is a hugely powerful tool that is worth learning.

Hope this helps.

Regards

Peter

 

[hma87]

Thank you for your reply. Sadly while this may be from an accounting tool (Doordash's) I do not have access to that, only the csv files they allow us to download (pictured).

 

[pjmorris]

That’s not a problem. You can use Powerquery to open the csv file directly. On the Get and Transform Tab chose Get From and then chose csv. You then navigate to wherever that is stored and transform it however you need.