SOMME.SI.ENS returns the #VALUE! error
Solved
Savon77
Posted messages
6
Status
Membre
-
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Hello everyone,
I'm reaching out because I have an issue that I can't resolve despite my research on various forums, etc., regarding the SUMIFS function.
I want to sum ranges on one sheet (Sheet 1) that are located on a second sheet (Sheet 2), using multiple criteria found on both Sheet 1 and Sheet 2.
My problem is that the function keeps returning the #VALUE! error without me understanding why. I have tried changing the cell formats (setting everything to number, setting everything to standard format, using text and number formats based on entries...) and enclosing my criteria in quotes, but nothing works.
Does anyone have an idea of the reason for this problem and how to solve it?
Here is a sample file to help you see my issue: https://www.cjoint.com/c/FFqkO6zA6D1
Thank you very much for your help!
P.S. Just so you know, I'm working with a Microsoft Home and Student 2013 version.
I'm reaching out because I have an issue that I can't resolve despite my research on various forums, etc., regarding the SUMIFS function.
I want to sum ranges on one sheet (Sheet 1) that are located on a second sheet (Sheet 2), using multiple criteria found on both Sheet 1 and Sheet 2.
My problem is that the function keeps returning the #VALUE! error without me understanding why. I have tried changing the cell formats (setting everything to number, setting everything to standard format, using text and number formats based on entries...) and enclosing my criteria in quotes, but nothing works.
Does anyone have an idea of the reason for this problem and how to solve it?
Here is a sample file to help you see my issue: https://www.cjoint.com/c/FFqkO6zA6D1
Thank you very much for your help!
P.S. Just so you know, I'm working with a Microsoft Home and Student 2013 version.
4 réponses
Hello,
I suspect that the error is due to Excel finding an inconsistency in the added cells and the searched cells.
The range arguments must always be the same in SUMIFS. This means that the criteria_range and sum_range arguments must refer to the same number of rows and columns.
However, you are asking Excel to conduct tests on 1 row or 1 column while you want to sum several.
Nevertheless, your problem can be solved.
I don't really understand why you are using SUMIFS; why not simply use VLOOKUP?
From what I see in your file, you want to know based on the year, the CS.
Your CS being composed with a number,
we can easily imagine a function:
=VLOOKUP(youryear, yourrange, RIGHT($B$3, 1) + 3, FALSE)
RIGHT($B$3, 1) + 3 will give you the 4th column for CS1, the 5th for CS2, etc. (Adjust according to your file)
Best regards,
Our only limits are those we impose on ourselves.
Politeness and a thank you do not hurt. There is a button for "Resolved" to confirm that your problem is no longer an issue. Fahora
I suspect that the error is due to Excel finding an inconsistency in the added cells and the searched cells.
The range arguments must always be the same in SUMIFS. This means that the criteria_range and sum_range arguments must refer to the same number of rows and columns.
However, you are asking Excel to conduct tests on 1 row or 1 column while you want to sum several.
Nevertheless, your problem can be solved.
I don't really understand why you are using SUMIFS; why not simply use VLOOKUP?
From what I see in your file, you want to know based on the year, the CS.
Your CS being composed with a number,
we can easily imagine a function:
=VLOOKUP(youryear, yourrange, RIGHT($B$3, 1) + 3, FALSE)
RIGHT($B$3, 1) + 3 will give you the 4th column for CS1, the 5th for CS2, etc. (Adjust according to your file)
Best regards,
Our only limits are those we impose on ourselves.
Politeness and a thank you do not hurt. There is a button for "Resolved" to confirm that your problem is no longer an issue. Fahora
