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 using SUM and XLOOKUP in W5.
Here the formular: =SUM(XLOOKUP(U5;Datum;West):XLOOKUP(V5;Datum;South))
I know it works on a US system, but unfortunately not on a European one when I enter it. Therefore a request. If someone would be so kind and calculate this or similar data according to the formula and send me the workbook. Only then Excel will "translate" the inputs correctly, I hope.
Unfortunately, this is not the first time, hence my request for help.
Thanks in advance.
11 Replies
=SUMPRODUCT(($B$2:$B$14>=$H$2)*($B$2:$B$14<=$I$2)*($E$2:$F$14))An alternative could be SUMPRODUCT.
thank you for the alternative solutions, fine and good, but my goal is different.
the following function / formulas (excerpt from a larger thing) in the us system works very well.
Formular / Function: =CHOOSE({1;2;3},{"a","b","c"},{"d","e","f"},{"g","h","i"})
Who has the imagination to show me this formula / function modified and no other in the European system? If your imagination fails you, no problem, I have the solution.
I think it's clear what I'm trying to get across after that.
- Not very clear about your question.
If possible, share your file and show expected result.
I don't think XLOOKUP is a good fit for this task. I'd go with FILTER:
=LET( filtered, FILTER($R$5:$S$11, (datum >= U5) * (datum <= V5), 0), SUM(filtered) )- Patrick2788
I agree, but I don't want teach a colleage the LET-functions too. 😉
SUM and XLOOKUP is so easy. 😎
On a US English system, you should use comma as list separator:
=SUM(XLOOKUP(U5,Datum,West),XLOOKUP(V5,Datum,South))
- Thanks, but this I tried too, but no positive result.
- 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.- 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.
