SOLVED

Sum of multiple rows using VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-3291279%22%20slang%3D%22en-US%22%3ESum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291279%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20are%20trying%20to%20use%20VLOOKUP%20in%20order%20to%20retrieve%20the%20multiple%20values.%20We%20have%20a%20value%20that%20we%20are%20looking%20for%20and%20we%20want%20to%20have%20the%20sum%20from%20all%20the%20cells%20that%20this%20value%20is%20related%20to.%20For%20example%2C%20say%20that%20the%20value%20we%20are%20looking%20for%20is%20%22Green%22%20and%20in%20our%20Excel%20file%20we%20have%20multiple%20rows%20that%20have%20%22Green%22%20entered%20and%20in%20a%20different%20collumn%20we%20have%20values%20that%20can%20be%202%20or%207%20or%2015%2C%20and%20what%20we%20want%20to%20do%20is%20gather%20all%20of%20this%20information%20into%20one%20cell.%20So%20if%20we%20have%20three%20rows%20that%20have%20%22Green%22%20and%20these%20different%20rows%20all%20have%20a%20different%20number%2C%20we%20want%20to%20collect%20all%20of%20these%20rows%20and%20not%20just%20get%20the%20first%20value%20that%20matches%20the%20search.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyone%20that%20can%20help%20us%20with%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3291279%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291364%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291364%22%20slang%3D%22en-US%22%3EOkay%20thank%20you.%3CBR%20%2F%3EThis%20worked%20for%20what%20I%20needed.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291353%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1369502%22%20target%3D%22_blank%22%3E%40anna3155%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20something%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(range_with_numbers%2C%20range_with_colors%2C%20%22Green%22%2C%20other_range%2C%20%22%26lt%3B%26gt%3BApple%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20only%20want%20to%20sum%20numbers%20where%20the%20color%20is%20green%20and%20another%20column%20does%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20contain%20Apple.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291321%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291321%22%20slang%3D%22en-US%22%3EOkay%20thank%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20any%20other%20option%20if%20I%20want%20to%20just%20exclude%20one%20criteria%3F%20or%20do%20I%20have%20to%20enter%20all%20the%20criteria%20that%20I%20want%20to%20include%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291308%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291308%22%20slang%3D%22en-US%22%3EYou%20can%20use%20SUMIFS()%20if%20you%20have%20multiple%20criteria%20and%20you%20can%20also%20use%20FILTER()%3CBR%20%2F%3E%3DSUMIFS(range-to-sum%2C%20range-with-colors%2C%20%22Green%22%2C%20range-with-other-info%2C%20other-criteria%2C%20...)%3CBR%20%2F%3E%3DSUM(%20FILTER(range-to-sum%2C%20(range-with-colors%20%3D%20%22Green%22)*(range-with-other-info%20%3D%20other-criteria)*...%20%2C%200)%20)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291302%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291302%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EThank%20you.%20What%20do%20I%20use%20if%20I%20want%20to%20exclude%20som%20of%20the%20cells%20that%20are%20included%20if%20I%20use%20%22SUMIF%22%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291301%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20multiple%20rows%20using%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291301%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1369502%22%20target%3D%22_blank%22%3E%40anna3155%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20VLOOKUP%2C%20use%20SUMIF%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIF(range_with_colors%2C%20%22Green%22%2C%20range_with_numbers)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@anna3155 

Instead of VLOOKUP, use SUMIF:

 

=SUMIF(range_with_colors, "Green", range_with_numbers)

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

@anna3155 

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.

Okay thank you.
This worked for what I needed.