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

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.

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

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

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

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

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

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