Sum lookup values?

I’m probably just missing how to do this, but is there a way to add the values returned in a lookup cell?

I have multiple rows with different data in one column. Is there a way to pull those values in and add them (SUM) in a different sheet? I’ve used the lookup functionality to pull these numbers into a single cell based on the relational data, but I’m stuck on how to add these together into a single result/cell from here. ie, Rather than having 1 5 10 as three separate numbers, how do I get a cell that shows 16 based on what I’m pulling in with the relational/lookup pulls.

I had the same question. Don’t use the lookup, use the relation column.

Thanks for the help and the quick reply. I saw your question, but I am unable to figure this out even from the response. The values are all different (and will be all across the board) - so how do I pull them in with a relation column? (I’ve used relations to gather inline lists, but not add/sum together).

You mentioned using a lookup to gather all the numbers into a single column based of off relational data. Create a Rollup column that summarizes the value of this relation by sum. If you cane figure it out, could you post a screen shot?

Here’s a few screen shots. In one sheet called “Bids” you can see the Cost column uses Math to calculate the cost for each row. The relations Column then shows which “Bid” these each belong to. In a separate tab called “Projects” I have used the Lookup function to pull in each of these costs columns. I can’t use a Rollup in the Projects tab to select the "Total Bid Costs (Lookup) Column.

If I use the rollup functionality in the bid sheet, it gives me the total costs for all bids, but not broken out by the relations or bid name. It sounds like you have an answer, but I’m not putting it together myself. Do these screen shots help? Any other ideas?

Thanks for the help.

Screen Shot 2020-02-27 at 11.08.52 AM

Yes. Can you do a Rollup (not lookup) by Relations (Green/Joni/Morgan) where you’re summing the Cost?

I don’t believe I can do a Rollup by one column (relations) where I SUM a different column (Costs) can I? When I create a Rollup and choose the Room Costs it rolls up the entire column (for all rooms). Is there a way to restrict this Rollup? Perhaps I’m not fully understanding Rollup well enough.

When setting up your Rollup column, scroll all the way down on the ‘summarize the values of’ dropdown. You should see your relation. Select that instead of the sheet name.

That was it! I wasn’t scrolling down to see the relations (prior to looking at the Sheets). Wow - thank you both very much for the help. Such a simple solution that had me stumped for the past two days on how to best solve. Thank you, thank you, thank you.

3 Likes