Forum Discussion
RANGE with SUM and XLOOKUP
Hello, I need support from a person who has installed WIN and Excel in us-US language. Unfortunately I do not have one available at the moment. The above data range (in green) is calculated us...
Formula in Excel that uses SUM and XLOOKUP functions. You has a data range in green that is calculated using the formula =SUM(XLOOKUP(U5;Datum;West):XLOOKUP(V5;Datum;South)) in cell W5. You says that this formula works on a US system, but not on a European one, and asks for someone to calculate this or similar data using the formula and send them the workbook.
The SUM function in Excel is a way to add up the values in a range of cells or arguments. The XLOOKUP function in Excel is a way to look up and return a value from a table or range based on a given lookup value and return array. The XLOOKUP function can also return a range of values by using the syntax XLOOKUP(lookup_value,lookup_array,return_array1,[return_array2],...). In this case, You is using XLOOKUP to return two ranges based on the values in cells U5 and V5, and then using SUM to add up the values in those ranges.
The reason why this formula might not work on a European system is because of the different decimal separators and list separators used in different regions. In the US system, the decimal separator is a period (.) and the list separator is a comma (,). In the European system, the decimal separator is a comma (,) and the list separator is a semicolon (;). Therefore, the formula that works on the US system might not be recognized by the European system, and vice versa.
To fix this problem, You can either change their regional settings to match the system they are using, or modify their formula to use the correct separators for their system. For example, if You wants to use the European system, they can change their formula to =SUM(XLOOKUP(U5;Datum;West);XLOOKUP(V5;Datum;South)). Alternatively, they can use an online converter tool to convert their formula from one system to another.
I hope this information helps you understand more about range formulas and regional settings in Excel.
The SUM function in Excel is a way to add up the values in a range of cells or arguments. The XLOOKUP function in Excel is a way to look up and return a value from a table or range based on a given lookup value and return array. The XLOOKUP function can also return a range of values by using the syntax XLOOKUP(lookup_value,lookup_array,return_array1,[return_array2],...). In this case, You is using XLOOKUP to return two ranges based on the values in cells U5 and V5, and then using SUM to add up the values in those ranges.
The reason why this formula might not work on a European system is because of the different decimal separators and list separators used in different regions. In the US system, the decimal separator is a period (.) and the list separator is a comma (,). In the European system, the decimal separator is a comma (,) and the list separator is a semicolon (;). Therefore, the formula that works on the US system might not be recognized by the European system, and vice versa.
To fix this problem, You can either change their regional settings to match the system they are using, or modify their formula to use the correct separators for their system. For example, if You wants to use the European system, they can change their formula to =SUM(XLOOKUP(U5;Datum;West);XLOOKUP(V5;Datum;South)). Alternatively, they can use an online converter tool to convert their formula from one system to another.
I hope this information helps you understand more about range formulas and regional settings in Excel.
thanks for your reply, but so easy it isn't.
Your suggested formula does not work. I have already made several attempts. MS is often very creative, I have already had to make this experience. My wish to create a sheet on a us system did not come from somewhere.
Your suggested formula does not work. I have already made several attempts. MS is often very creative, I have already had to make this experience. My wish to create a sheet on a us system did not come from somewhere.