#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 440
Default VLOOKUP & SUMIF

I wish to somehow combine these two functions so that if "Ford" for instance
appears six times in column 'B' then the SUMIF part then returns the sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: VLOOKUP & SUMIF

Hi Jock,

You can definitely combine VLOOKUP and SUMIF functions to achieve the desired result. Here's how you can do it:
  1. First, you need to create a unique list of all the car brands that appear in column B. You can do this by selecting the entire column B, going to the Data tab, and clicking on the "Remove Duplicates" button.
  2. Next, you need to create a formula that will return the sum of values in column C for each car brand. Here's the formula you can use:
    Code:
    =SUMIF(B:B, "Ford", C:C)
    In this formula, "Ford" is the car brand you want to sum the values for. You can replace it with any other car brand you want to calculate the sum for.
  3. To make this formula work for all the car brands in your unique list, you can use the VLOOKUP function. Here's the formula you can use:
    Code:
    =VLOOKUP(E2, B:C, 2, FALSE)
    In this formula, E2 is the cell where you have the first car brand in your unique list. B:C is the range where you have the car brands and their corresponding values. 2 is the column number where you have the values you want to sum (in this case, column C). FALSE ensures that the exact match is found.
  4. Copy this formula down to all the cells in the column where you have your unique list of car brands.
  5. You should now have a list of all the car brands and their corresponding sums in the adjacent column.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default VLOOKUP & SUMIF

That's what SUMIF() does

=SUMIF(B2:B20,"Ford",C2:C20)

"Jock" wrote:

I wish to somehow combine these two functions so that if "Ford" for instance
appears six times in column 'B' then the SUMIF part then returns the sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default VLOOKUP & SUMIF

Try this:-

=IF(COUNTIF(B1:B100,"=Ford")=6,SUMIF(B1:B100,"=For d",C1:C100),"not six fords
in this list")

Mike

"Jock" wrote:

I wish to somehow combine these two functions so that if "Ford" for instance
appears six times in column 'B' then the SUMIF part then returns the sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 440
Default VLOOKUP & SUMIF

Thanks gents.
--
tia

Jock


"Mike H" wrote:

Try this:-

=IF(COUNTIF(B1:B100,"=Ford")=6,SUMIF(B1:B100,"=For d",C1:C100),"not six fords
in this list")

Mike

"Jock" wrote:

I wish to somehow combine these two functions so that if "Ford" for instance
appears six times in column 'B' then the SUMIF part then returns the sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ari Ari is offline
external usenet poster
 
Posts: 12
Default VLOOKUP & SUMIF

But how do you include sumif in a vlookup such that the sum of multiple
instances is returned; for example if Ford appears three time with $1, $2, &
$9, $12 is returned for the name Ford, because otherwise a regular vlookup
will return the value of the first instance found.

Thanks,

Ari

"Duke Carey" wrote:

That's what SUMIF() does

=SUMIF(B2:B20,"Ford",C2:C20)

"Jock" wrote:

I wish to somehow combine these two functions so that if "Ford" for instance
appears six times in column 'B' then the SUMIF part then returns the sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default VLOOKUP & SUMIF

Hi Ari

Supposing column A held a,b,c etc. and column B held Mercedes, Ford, GM etc
Also, column F held a mixture of those company names, and column G held
values associated with each entry

Now, enter b in cell C1 and use the formula
=SUMIF(F:F,VLOOKUP(C1,A:B,2,0),G:G)
and it will return the Sum of the values relating to Ford
--
Regards
Roger Govier

"Ari" wrote in message
...
But how do you include sumif in a vlookup such that the sum of multiple
instances is returned; for example if Ford appears three time with $1, $2,
&
$9, $12 is returned for the name Ford, because otherwise a regular vlookup
will return the value of the first instance found.

Thanks,

Ari

"Duke Carey" wrote:

That's what SUMIF() does

=SUMIF(B2:B20,"Ford",C2:C20)

"Jock" wrote:

I wish to somehow combine these two functions so that if "Ford" for
instance
appears six times in column 'B' then the SUMIF part then returns the
sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ari Ari is offline
external usenet poster
 
Posts: 12
Default VLOOKUP & SUMIF

Thanks Roger; it worked like magic.

Ari

"Roger Govier" wrote:

Hi Ari

Supposing column A held a,b,c etc. and column B held Mercedes, Ford, GM etc
Also, column F held a mixture of those company names, and column G held
values associated with each entry

Now, enter b in cell C1 and use the formula
=SUMIF(F:F,VLOOKUP(C1,A:B,2,0),G:G)
and it will return the Sum of the values relating to Ford
--
Regards
Roger Govier

"Ari" wrote in message
...
But how do you include sumif in a vlookup such that the sum of multiple
instances is returned; for example if Ford appears three time with $1, $2,
&
$9, $12 is returned for the name Ford, because otherwise a regular vlookup
will return the value of the first instance found.

Thanks,

Ari

"Duke Carey" wrote:

That's what SUMIF() does

=SUMIF(B2:B20,"Ford",C2:C20)

"Jock" wrote:

I wish to somehow combine these two functions so that if "Ford" for
instance
appears six times in column 'B' then the SUMIF part then returns the
sum of
the values relating to "Ford" from column 'C'.
--
tia

Jock


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif/vlookup/left Gingit Excel Discussion (Misc queries) 4 April 21st 07 12:32 AM
VLOOKUP, SUMPRODUCT, or SUMIF? steph Excel Worksheet Functions 5 March 28th 07 04:26 PM
VLOOKUP/ SUMIF not sufficient Morgwen Excel Discussion (Misc queries) 0 June 15th 05 03:26 AM
Do I need a sumif or sum of a vlookup formula? PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM
match mix with sumif and vlookup Manos Excel Worksheet Functions 3 February 28th 05 12:29 PM


All times are GMT +1. The time now is 12:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"