Number to Text with Thousand Separator and Two Decimal Numbers

[ellatrj]

Hi there
I tried to search in the forum if it has already been asked, but couldn't find it. So sorry if it has been answered, kindly post the link to it.
Otherwise please help with this question:

I have numbers from 0- 1'000'000.00 and I tried the following formula:

=TEXT(ROUNDUP(XLOOKUP(@$D:$D;'Sum up'!$A:$A;'Sum up'!B:B;"0";0)*1);2);"#'##0.00")

but then I have the following problems:

numbers under 1000 are formatted like this, with a leading thousand separator: '100.00
'10.00
'0.00

and numbers over million like this: 1234'000.00 , so one separator is missing.

How can I adjust my formula above to get rid of these two problems?

Many thanks in advance!

I want it like in the table below:

 

[aaewalsh]

=TEXT(IF(ROUNDUP(XLOOKUP(@$D:$D,'Sum up'!$A:$A,'Sum up'!B:B,"0",0)*1)<1000000, ROUNDUP(XLOOKUP(@$D:$D,'Sum up'!$A:$A,'Sum up'!B:B,"0",0)*1),"#,##0.00"), ROUNDUP(XLOOKUP(@$D:$D,'Sum up'!$A:$A,'Sum up'!B:B,"0",0)*1),"#,##0,,")

 

[ellatrj]

Hey aaewalsh, thanks for the quick response, but unfortunately I get "you have entered too many arguments for this function"...could you troubleshoot where I have to correct something?:

=IF(ROUNDUP(XLOOKUP(@$D:$D;'Sum up'!$A:$A;'Sum up'!B:B;"0";0)<1000000;
ROUNDUP(XLOOKUP(@$D:$D;'Sum up'!$A:$A;'Sum up'!B:B;"0";0);"#'##0.00");
ROUNDUP(XLOOKUP(@$D:$D;'Sum up'!$A:$A;'Sum up'!B:B;"0";0);"#'##0,,"))

 

[Tetra201]

Try using the following format instead of "#'##0.00":

=TEXT(...,"[<1000]#0.00;[<1000000]#'###.00;#'###'###.00")