VLOOKUP: Sum Multiple Row Entries

[DBlake]

Hi folks,

I have a spreadsheet with multiple customers and account numbers with revenue.
I am trying to sum the revenue for multiple instances of the same brand name by using VLOOKUP

Review table (on Sheet 1):

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Brand Name[/TD]
[TD]Total Revenue[/TD]
[/TR]
[TR]
[TD]Alfa Romeo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mercedes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Source Table (on Sheet 2):

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Brand Name[/TD]
[TD]Account #[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Alfa Romeo[/TD]
[TD]853687[/TD]
[TD]125000[/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD]985632[/TD]
[TD]85000[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]789456[/TD]
[TD]105000[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]125478[/TD]
[TD]95000[/TD]
[/TR]
[TR]
[TD]Mercedes[/TD]
[TD]875421[/TD]
[TD]130000[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]852147[/TD]
[TD]110000[/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD]147852[/TD]
[TD]150000[/TD]
[/TR]
[TR]
[TD]Alfa Romeo[/TD]
[TD]654123[/TD]
[TD]120000[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]451278[/TD]
[TD]90000[/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD]978645[/TD]
[TD]80000[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]258147[/TD]
[TD]110000[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]349943[/TD]
[TD]950000[/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD]875634[/TD]
[TD]150000[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]567890[/TD]
[TD]80000[/TD]
[/TR]
</tbody>[/TABLE]


Is it possible use VLOOKUP to sum the revenues based on the multiple same brand names?
For instance- there are 4 instances of Porsche, with a sum result of 465,000. I need that sum populated on my review sheet.
I know a Pivot table would get me the result - but there are other categories and data on my review sheet, so I need this in a regular cell format.

Thank you in advance for the support!

 

[Eric W]

Try SUMIF:

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Brand Name[/TD]
[TD="bgcolor: #FAFAFA"]Total Revenue[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Alfa Romeo[/TD]
[TD="bgcolor: #FAFAFA, align: right"]245000[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Porsche[/TD]
[TD="bgcolor: #FAFAFA, align: right"]465000[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]BMW[/TD]
[TD="bgcolor: #FAFAFA, align: right"]385000[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Mercedes[/TD]
[TD="bgcolor: #FAFAFA, align: right"]130000[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Audi[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1155000[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUMIF(Sheet2!A:A,A2,Sheet2!C:C)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Have to tried SUMIF?

=SUMIF(Sheet2!A:A,A2,Sheet2!C:C)

 

[DBlake]

Don't know why I didn't think of that. Thanks, guys!!

There is one caveat though - say I have an account that has a variance in the name (which I do, unfortunately):
i.e.: BMW, BMW North, BMW 1...
I was really hoping that using VLOOKUP with the TRUE range_lookup would identify those closest matches and sum them together...
Any idea if that's possible?

Thanks again!

 

[63falcondude]

Try adding in wildcards

=SUMIF(Sheet2!A:A,"*"&A2&"*",Sheet2!C:C)

 

[DBlake]

ray:ray:ray:ray: I couldn't find a Mind-Blown emoji.
I never even knew about wildcards. Smarter every day!
Thanks so very much!

 

[63falcondude]

ray:ray:ray:ray: I couldn't find a Mind-Blown emoji.
I never even knew about wildcards. Smarter every day!
Thanks so very much!

Great, glad to help! Just note that "*BMW*" will sum everything with "BMW" in it. (e.g. "Audi of BMWalter" would count for Audi and BMW) Its a crazy scenario but something to keep in mind.

 

[DBlake]

Good to know. It will save me from future headaches and unnecessary postings here lol
Thanks again very very much - have a great day!!

 

[phoenixdown]

Keep in mind SUMIFS which is like SUMIF but allows multiple criteria. i.e. Sum of Customer Name + Month

 

[VIJAYSARDE]

Awosome

Try SUMIF:

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Brand Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Total Revenue[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Alfa Romeo[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]245000[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Porsche[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]465000[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BMW[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]385000[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mercedes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]130000[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Audi[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1155000[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUMIF(Sheet2!A:A,A2,Sheet2!C:C)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]