SUMIF using a cell with a formula in it

Kirstine · 11/07/18

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?

Andrée Starå · 11/07/18

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 · 11/07/18

Hi André,

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

K

Paul Newcome · 11/07/18

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

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

Andrée Starå · 11/08/18

Happy to help! (Paul beat me to it)

Kirstine · 11/08/18

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 · 11/08/18

{BRAIN v1.0 Range 1}

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

Paul Newcome · 11/08/18

Sorry for stealing your thunder...

Andrée Starå · 11/08/18

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

Kirstine · 11/08/18

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

Paul Newcome · 11/08/18

Fair enough. Lol

Paul Newcome · 11/08/18

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 · 11/08/18

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

Paul Newcome · 11/08/18

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 · 11/08/18

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 · 01/17/19

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

SUMIF using a cell with a formula in it

Comments

Help Article Resources

Categories