Using a Vlookup and Sumifs in the same sheet ?

We have a master job list which we use to create our new project references. There are a lot of requests with a range of values ? I have a separate sheet where we populate with those projects we want to keep a closer eye on costs on picked up from other smartsheets ? The process being, I type in the job reference and it populates the description fields etc using Vlookup which is perfect, but then we add -1, -2 for variations etc. I want to try and use sumifs to calculate the overall value for all rows with the same job number ? For Example 215468, 214568-1, 215468-2 etc but can not find a formula which doesn't rely on an exact match ?

In Job No I type in Job No 215468

In following columns:-

=VLOOKUP($[Job No]17, {Master Project List}, 4, false) - Returns site name

=SUMIFS({Master Project and Job List Sell}, {Master Project and Job List Range 1}, $[Job No]17) - Returns its value

But on the master Project list there is also 215468-1, 215468-2 etc All with the same site name but different values which i just want to sum against the one row ?

Thanks

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!