Formula Question

sowell325

A) I have a sheet with a drop-down menu that I would like a cell to "find" in another sheet and give me that description.

THEN

B) IN the Bse Price Column, I need it to go find the price for that model id and feed to that cell

THEN

C) I have the add-on column and need it to go find the price for the ones I have selected from the add-ons column, add them up, and put the total in the add-on's price column.

Can anyone do this - I am struggling! LOL

KPH

When your column name contains a space you must use square brackets around the name in the formula:

Try this:

=SUMIF({products Range 2}, CONTAINS(@cell, [Model ID]@row ), {products Range 3})

KPH

I think I understand your use case.

You have two sheets, one is a reference sheet (the second screenshot) which has prices, description, and hopefully model number.

On the other sheet, you enter a model number and want to return the description and price (and add on price) from the reference sheet. I am assuming you only enter one model number per row (the drop down is not multi-select).

If so, I think an index collect formula would work for you. Take a look at https://help.smartsheet.com/function/index

You'll need to use cross sheet references (not sure if you are familiar with those).

Your formulas will be something like

=INDEX(COLLECT({cross sheet reference for the description}, {cross sheet reference for model number}, [Model ID}@row), 1)

=INDEX(COLLECT({cross sheet reference for the price}, {cross sheet reference for model number}, [Model ID}@row), 1)

Let us know how you get on, and we can provide more help, if needed.

sowell325

How or what formula what I use to take multi "Add On's" column and add the price of those up?

DKazatsky2

Hi @sowell325,

For getting the "Add On's" pricing try this.

=SUMIF({cross sheet reference for products}, CONTAINS(@cell, Products@row), {cross sheet reference for price})

Hope this helps,

Dave

sowell325

So I used

=SUMIF({products Range 2}, CONTAINS(@cell, Model ID@row ), {products Range 3})

products Range 2 is the group of cells that I am referencing for the Model ID or products

products Range 3 is the group of cells that I am referencing for the price

CONTAINS(@cell, Model ID@row ) is the cell with the drop-down of the products in the order

What am I doing wrong here?

and it gives me a #UNPARSEABLE

KPH

When your column name contains a space you must use square brackets around the name in the formula:

Try this:

=SUMIF({products Range 2}, CONTAINS(@cell, [Model ID]@row ), {products Range 3})

sowell325

OMG!!! IT WORKED!!!!!!!!!

THANK YOU!!!!

KPH

Wonderful!

sowell325

I did the same formula

=SUMIF({products Range 3}, CONTAINS(@cell, [Add On's]@row ), {products Range 2})

But it is just giving me $ 0.00

sowell325

I had it backwords on the range - so I got that to work —

But it is not adding multi products up :(

KPH

Do you have it this way around?

=SUMIF({Range that is the Products Name column}, CONTAINS(@cell, [Add On's]@row), {Range that is the Products Price column})

I've mocked up an illustration:

Reference sheet:

sowell325

Yes that is how I have it.

KPH

Can you tell what is happening? Is only one price being returned? If so, which ones are missing? Do the names in both sheets exactly match?

sowell325

Yes it not adding then up

KPH

It looks like it isn't returning any - just $0. Can you check the cross-sheet references are correct? And try just one add on and see if that works.

sowell325

Correct its not returning any amounts no matter what I changed the drop down to (select).