# SUMIF using a cell with a formula in it

✭✭✭✭✭✭
edited 12/09/19

OK, so my formula is this: =SUMIF([Customer PO]:[Customer PO], [Customer PO]2, [Value to be invoiced]:[Value to be invoiced]). I'm pretty sure it's correct, but the 'Customer PO" column uses a VLOOKUP formula which I think is what's causing a #NO MATCH error...?

Is there any way to get around this?

Tags:
«1

• ✭✭✭✭✭✭

Hi Kirstine,

The formula looks correct.

Can you share a screenshot or the sheet?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Hi André,

Thanks for your response. It's a bit tricky with a screenshot! Have a look and see if this helps.

K

• ✭✭✭✭✭✭

Try wrapping your VLOOKUP in a VALUE statement like so...

=VALUE(VLOOKUP(.............................))

• ✭✭✭✭✭✭

Happy to help! (Paul beat me to it)

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Unfortunately it didn't work, I got an #INVALID VALUE error.

This was my formula: =VALUE(VLOOKUP([Our ref]360, {BRAIN v1.0 Range 1}, 19, false))

• ✭✭✭✭✭✭

{BRAIN v1.0 Range 1}

What does this reference? Have you double checked your column number?

• ✭✭✭✭✭✭

• ✭✭✭✭✭✭

It's okay as long as you borrow and return it again

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

The 'Brain' reference is another sheet. The VLOOKUP was working fine until I added the VALUE.

• ✭✭✭✭✭✭

Fair enough. Lol

• ✭✭✭✭✭✭

Ok. I looked again and see where the VALUE suggestion was my mistake.

Try below...

=SUMIF([Customer PO]:[Customer PO], @cell = [Customer PO]2, [Value to be invoiced]:[Value to be invoiced])

• ✭✭✭✭✭✭

Ha ha! Thanks for persevering! Unfortunately this doesn't work either, I get an #INVALID DATA TYPE, sorry!

• ✭✭✭✭✭✭

Are you able to "Save as new" for each applicable sheet, replace sensitive data with test data, then publish a link to the sheets? We would be able to get in there and see exactly what's going on and play around with it a little bit that way.

• ✭✭✭✭✭✭

Grr... Lets try using a different function...

=SUMIFS([Value to be invoiced]:[Value to be invoiced], [Customer PO]:[Customer PO], @cell = [Customer PO]2)

If that doesn't work, try removing the @cell =

• ✭✭✭✭✭✭
edited 01/17/19

Were you able to find a solution to this? After taking another look... Are you trying to SUM or COUNT?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!