Forum Discussion

Copper Contributor

Mar 17, 2022

Solved

Issues using Ampersand (&) to combine numbers from two different cells

I am trying to combine two different cells that contain numbers into one cell and it is bringing over incorrect values. In my example below the first part of the data is in cell B2 and the second pa...

excel

Formulas and Functions

Detlef_Lewin
Mar 17, 2022
Mbala365
So, the invoice number is the criteria, right?
Then you can leave it as text but you hit another problem because SUMIF() makes a type conversion from text to number and the result is not correct.
But there is a workaround:
https://newtonexcelbach.com/2017/12/22/the-countif-bug-and-how-to-avoid-it/

Mbala365

Copper Contributor

Mar 17, 2022

I do need to do further calculations as I am using a SUMIF formula to total the amounts received based on the special invoice number. Do you know of any add one as you suggested?

Detlef_Lewin

Silver Contributor

Mar 17, 2022

Mbala365

So, the invoice number is the criteria, right?

Then you can leave it as text but you hit another problem because SUMIF() makes a type conversion from text to number and the result is not correct.

But there is a workaround:

https://newtonexcelbach.com/2017/12/22/the-countif-bug-and-how-to-avoid-it/

Mbala365
Copper Contributor
Mar 18, 2022
Detlef_Lewin That worked perfectly!! Thanks so much for giving that additional resource as I used the CHAR(173)& and it worked like a charm! Have a great day and thanks again!!