SUMIF using a cell with a formula in it

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?

Find more posts tagged with

Construction

Finance

Formulas

Comments

Andrée Starå

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

Kirstine

Hi André,

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

Paul Newcome

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

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

Andrée Starå

Happy to help! (Paul beat me to it)

Kirstine

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))

Paul Newcome

{BRAIN v1.0 Range 1}

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

Paul Newcome

Sorry for stealing your thunder...

Andrée Starå

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

Kirstine

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

Paul Newcome

Fair enough. Lol

Paul Newcome

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])

Kirstine

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

Paul Newcome

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.

Paul Newcome

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 =

Paul Newcome

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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?