Using a Vlookup and Sumifs in the same sheet ?

Rob Pritchard

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

Andrée Starå

Hi Rob,

Try using the CONTAINS function.

More info: https://help.smartsheet.com/function/contains

Did it work?

I hope that helps!

Have a fantastic weekend and let me know if you have any questions!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Rob Pritchard

Thanks for the quick response. I hand tried the contains but can not get it to work within a SUMIFS, any thoughts or examples?

Andrée Starå

Happy to help!

Try something like this. Change the names of the columns and ranges to match yours.

=SUMIFS({Range Sell}, {Range JobNo}, CONTAINS(JobNo@row, @cell))

Did it work?

Rob Pritchard

Hi Andree,

Thanks again but still no joy. I am not the most confident with the @row and @cell commands ?

This formula works when the number in the Scenariio Job No exactly matches the one in the Project Job List Smartsheet but does not pickup other job numbers which are the same but have a -1, -2 after ?

=SUMIFS({Master Project and Job List Sell}, {Master Project and Job List Range 1}, $[Scenariio Job No]18)

Regards

Rob

Andrée Starå

Try this one.

=SUMIFS({Master Project and Job List Sell}, {Master Project and Job List Range 1}, CONTAINS($[Scenariio Job No]@row, @cell))

If the above formula doesn't work, can you maybe share the sheet(s) or copies of them? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Rob Pritchard

Thanks again...and almost perfect but it ignores the actual master number without the -1 etc ? But it adds up all the other versions perfectly.

Regards

Rob

Andrée Starå

I had some issues as well but when I tried this one it worked with:

215468

215468-1

215468-2

What type of columns are they?

Rob Pritchard

Hi...Apologies for not getting back to you last night. It does work when the master cell is blank ? As soon as there is a value in the cell it only sums that one ?

215468 - £6000

215468-1 £2000

215468-2 £2000

Formula returns £6000

215468 - 0

215468-1 £8000

215468-2 £2000

Formula returns £10,000

Thanks

Andrée Starå

That's so strange!

Can you double check the column types? What type are they?

Rob Pritchard

Yes that's what I thought. It looked as if it was perfect but noticed a couple of sums did not add up and realised it was the master row ? We do indent all the rows below but can not use the children sum as that messes up some other high level numbers we report on ?

It is the primary column, just a text field for the Job No ? and a Text / Number field for the Sell.

Thanks

Andrée Starå

@Rob Pritchard I'll get back to you if I find why it's not consistent.

Rob Pritchard

@Andrée Starå ..we still have the same problem even with the @row and @cell commands. Just wondering if anymore thoughts?

Andrée Starå

@Rob Pritchard

Unfortunately, no. I'd need to look at it in more detail to see what's possible.