Forum Discussion
Sum of multiple rows using VLOOKUP
We are trying to use VLOOKUP in order to retrieve the multiple values. We have a value that we are looking for and we want to have the sum from all the cells that this value is related to. For example, say that the value we are looking for is "Green" and in our Excel file we have multiple rows that have "Green" entered and in a different collumn we have values that can be 2 or 7 or 15, and what we want to do is gather all of this information into one cell. So if we have three rows that have "Green" and these different rows all have a different number, we want to collect all of these rows and not just get the first value that matches the search.
Is there anyone that can help us with this?
You can use something like
=SUMIFS(range_with_numbers, range_with_colors, "Green", other_range, "<>Apple")
if you only want to sum numbers where the color is green and another column does not contain Apple.
6 Replies
- Hello,
Thank you. What do I use if I want to exclude som of the cells that are included if I use "SUMIF"?- You can use SUMIFS() if you have multiple criteria and you can also use FILTER()
=SUMIFS(range-to-sum, range-with-colors, "Green", range-with-other-info, other-criteria, ...)
=SUM( FILTER(range-to-sum, (range-with-colors = "Green")*(range-with-other-info = other-criteria)*... , 0) )
