 SOLVED

# 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?

6 Replies

# Re: Sum of multiple rows using VLOOKUP

=SUMIF(range_with_colors, "Green", range_with_numbers)

# Re: Sum of multiple rows using VLOOKUP

Hello,
Thank you. What do I use if I want to exclude som of the cells that are included if I use "SUMIF"?

# Re: Sum of multiple rows using VLOOKUP

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) )

# Re: Sum of multiple rows using VLOOKUP

Okay thank you.

Is there any other option if I want to just exclude one criteria? or do I have to enter all the criteria that I want to include?
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Sum of multiple rows using VLOOKUP

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.

# Re: Sum of multiple rows using VLOOKUP

Okay thank you.
This worked for what I needed.