SUMIF using a cell with a formula in it

Kirstine

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å

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. 

K

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?