How do you calculate a total from a list from multiple sheets?

DB393

I have (3) sheets that I have a long list of services and the cost associated with them. Which I can combine in (1) sheet if needed...

On what I will refer to as my destination sheet, I have aggregated the services selected by a client and I need a formula that will reference the cell that has all of the services listed in them, look at the cost sheet(s) and calculate a total.

Example:

Destination Cell has the services: Bookkeeping, Reporting, and Sales Training

Sheet 1 has Bookkeeping and the cost is $150

Sheet 2 has Reporting and the cost is $150

Sheet 3 has Sales Training and the cost is $500

The desired result would be a total cost in the formula cell that should show $800.

This would be easy in excel, however I am not sure if the logic is the same in Smartsheet or if it even has the capability of something like this?

TIA for your assistance/recommendations!

Paul Newcome

You could use an INDEX/MATCH (one for each sheet) to pull in the amount and then add them together.

=INDEX/MATCH + INDEX/MATCH + INDEX/MATCH

DB393

I moved all of the services and costs to one sheet.

So essentially I need a formula that will look at services@row and based on what items that are within the cell look at the helper sheet and add them together.

services@row has bookkeeping Reporting and Sales Training (not sure if it matters but I have the delimiter as char(10))

Helper sheet has (2) columns service name and revenue.

in my formula cell for it to = $800 (cost/revenue, from prior post) do I need to add sumif to the formula?

would you be able to provide an example formula from the information that I might be able to try?

Thank you again @Paul Newcome!

Paul Newcome

I would suggest a SUMIFS so you can sum the $ column if it has a match in the services column.

Edited to add link:

https://help.smartsheet.com/function/sumifs

DB393

Okay I keep getting errors,

cross sheet reference for revenue= {sdr}

cross sheet reference for service name (Deliverables)= {sdl}

would you be able to assist me with the formula that would populate the total revenue for these services?

I am getting lost in using the sumifs with the index/match formula...

Paul Newcome

You won't need an INDEX/MATCH. Just the SUMIFS. You will also need to incorporate a HAS function in the criteria for the deliverables piece.

Try a SUMIFS with a HAS, and post what you use and what you get here, and then we can work through where you need help.

DB393

=SUMIFS({sdr}, {sdl}, HAS(Deliverables@row, @cell))

The desired result should be: $2,940.00

Paul Newcome

Ok. So this is actually an easy fix if we take a look at the intended function of HAS.

HAS is designed to find "text string" inside of a multi-select cell. What we want to do is find a multi-select cell inside of some "text strings". We are working in the opposite direction of the intended use, so try flipping the arguments around inside of the HAS function so that they too are opposite of the intended syntax.

HAS(@cell, Deliverables@row)

DB393

=SUMIFS({sdr}, {sdl}, HAS(@cell, Deliverables@row))

Same result.....

FYI this is my helper sheet where I am using the cross sheet functions.

Paul Newcome

How are you populating the $$ column in the helper sheet?

DB393

Free Type

I entered values manually.

DB393

@Paul Newcome any idea why it is not populating a value? Let me know if there is any additional information that I can provide to get the desired results. Thank you!

Paul Newcome

And can you show a few of the entries in the Deliverables column from the reference table that should be matching?

DB393

I was able to get this formula to populate a $ amount however, this formula will only work if there is only (1) Deliverable in the cell. If there are (2)+ deliverables it shows $0.00.

Is there a way to adjust the formula so that it looks at all of the Deliverables in the cell and then sum the amount?

Paul Newcome

The HAS function would be how to do that.

Can you provide screenshots of a cell that has the data you are wanting to match on from the formula sheet and a screenshot of the reference table that shows the row(s) that it should be pulling from?

I am trying to compare the two data sets but can't unless I have a visual on something that is supposed to be working.

DB393

@Paul Newcome I was able to work out the solution. Posting formula for the community.

Here is the formula that I ended up using: =SUMIFS({sdr}, {sdl}, HAS(Deliverables@row, @cell)

Thank you for your help!