Simple multiplying formula question

Genevieve P.

Hi @Brittany Lucarell

It looks like you're missing [these brackets] around your column name at the end. Try this:

=IF(Qty@row = "", "", Qty@row * [Unit Cost]@row)

When column names have spaces or end in a number, they need to be surrounded by square brackets so the formula can read the start and end of the column name. You can read about this in our Help Center article (here).

Let me know if you have any other questions!

Genevieve

Brittany Lucarell

@Genevieve P - This still did not work, I am even trying to just do a simple formula of Unit Cost * Qty and this will not work either

Genevieve P.

Hi Brittany,

It sounds like maybe the column names are different than what's in the formula, is that possible?

Could you provide a screen capture of your sheet with column names visible (blocking out any sensitive data).

Thanks!

Brittany Lucarell

@Genevieve P sure please see below screen capture and the formula that I tried using.

Genevieve P.

@Brittany Lucarell

Thank you, this is great! So it looks like your Unit Cost is coming through as Text instead of as a Number Value, which is why you're getting this error (you can't multiply text with a number). The way I can tell is that it's left-aligned in the cell, instead of right-aligned (like your Qty numbers).

How are you getting that Unit Cost number?

In any case, you can correct this by adding the VALUE function around that Unit Cost reference in your formula. (See here for information on the Value function.)

Try this:

=VALUE([Unit Cost]@row) * Qty@row

If this doesn't work, we could potentially adjust the formula returning your Unit Cost to make sure it's coming in as a number instead of as text.

Brittany Lucarell

@Genevieve P below is probably why the Unit cost is coming through as test. I made an IF formula to auto populate the cost for which Description item was chosen from drop down box.

I tried using the Value formula and unfortunately that is still not working. Is there something else I should use to auto populate the Unit Cost when choosing the product?

Genevieve P.

Hi @Brittany Lucarell

Yes, that would be it!

You don't need to add the "quotes" around the number or the $ sign... that's why it's reading as text. Try just adding the number itself, whenever you want a $amount to show up.

Ex:

=IF([Description Item]@row = "Waxie No-Touch Foam Handwash Dispenser", 13.53, IF(etc...

Then click on the name of the column, Unit Cost, and select the Currency option from the toolbar menu at the top of your sheet. This will add a $ sign in front of any numbers, but will ignore any text:

You can read more about the currency format in our Help Center (see here).

That should do it! If not...it would be helpful to see the entire formula copied/pasted to figure out what's going on. Let me know if it works.

Brittany Lucarell

That worked!!! Thank you so much!!

Genevieve P.

Woohoo! Happy to help. 🙂