I have a list in an Excel worksheet that is also used as a VLOOKUP table to
calculate values applied to various products, specifically, varietal names of
fruits such as apples, pears, cherries etc. Each fruit has several names of
its varieties listed in the table (as an example apples are listed as Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values in a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of dependent
cells for only a specific fruit i.e. apple varieties, then pear varieties,
etc. so that I end up with total values by fruit for apples, pears, cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

Post your layout
apple gala
apple fuji
etc.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table to
calculate values applied to various products, specifically, varietal names
of
fruits such as apples, pears, cherries etc. Each fruit has several names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values in
a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of dependent
cells for only a specific fruit i.e. apple varieties, then pear varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

to count
=SUMPRODUCT((J1:J21={"gala","fuji"})*1)
to sum col K
=SUMPRODUCT((J1:J21={"gala","fuji"})*k1:k21)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Post your layout
apple gala
apple fuji
etc.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table
to
calculate values applied to various products, specifically, varietal
names of
fruits such as apples, pears, cherries etc. Each fruit has several names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values
in a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of
dependent
cells for only a specific fruit i.e. apple varieties, then pear
varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

First ... a comment:

It is *not* necessary to sort a datalist for the Vlookup() function to
calculate properly, as long as you're looking for an exact match, and you
use zero ( 0 ) or "False" as the 4th argument in the Vlookup() function.

That being said, you might be able to use the Countif() function for your
purposes, but since you gave no indication of your data configuration, it's
difficult to determine if it (Countif) will work.

For example, with fruit in every row of Column A,where the variety is in the
corresponding row of Column B:

=Countif(A1:A100="apple")

If this doesn't work, post back with your data set-up.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------


"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table to
calculate values applied to various products, specifically, varietal names
of
fruits such as apples, pears, cherries etc. Each fruit has several names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values in
a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of dependent
cells for only a specific fruit i.e. apple varieties, then pear varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

Thank you for your suggestion Don. Since you conclusion worked for me, I
will not post the layout.
Thank you again.
--
Dewayne


"Don Guillett" wrote:

Post your layout
apple gala
apple fuji
etc.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table to
calculate values applied to various products, specifically, varietal names
of
fruits such as apples, pears, cherries etc. Each fruit has several names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values in
a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of dependent
cells for only a specific fruit i.e. apple varieties, then pear varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

Thanks for clarification on sorting a list for VLOOKUP. It was my
understanding & experience that a list need to be sorted in ascending order
for it to work properly. I guess I was always doing some wrong.
Thank you again
--
Dewayne


"RAGdyer" wrote:

First ... a comment:

It is *not* necessary to sort a datalist for the Vlookup() function to
calculate properly, as long as you're looking for an exact match, and you
use zero ( 0 ) or "False" as the 4th argument in the Vlookup() function.

That being said, you might be able to use the Countif() function for your
purposes, but since you gave no indication of your data configuration, it's
difficult to determine if it (Countif) will work.

For example, with fruit in every row of Column A,where the variety is in the
corresponding row of Column B:

=Countif(A1:A100="apple")

If this doesn't work, post back with your data set-up.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------


"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table to
calculate values applied to various products, specifically, varietal names
of
fruits such as apples, pears, cherries etc. Each fruit has several names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values in
a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of dependent
cells for only a specific fruit i.e. apple varieties, then pear varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dewayne" wrote in message
...
Thank you for your suggestion Don. Since you conclusion worked for me, I
will not post the layout.
Thank you again.
--
Dewayne


"Don Guillett" wrote:

Post your layout
apple gala
apple fuji
etc.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table
to
calculate values applied to various products, specifically, varietal
names
of
fruits such as apples, pears, cherries etc. Each fruit has several
names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values
in
a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of
dependent
cells for only a specific fruit i.e. apple varieties, then pear
varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Dewayne" wrote in message
...
Thanks for clarification on sorting a list for VLOOKUP. It was my
understanding & experience that a list need to be sorted in ascending order
for it to work properly. I guess I was always doing some wrong.
Thank you again
--
Dewayne


"RAGdyer" wrote:

First ... a comment:

It is *not* necessary to sort a datalist for the Vlookup() function to
calculate properly, as long as you're looking for an exact match, and you
use zero ( 0 ) or "False" as the 4th argument in the Vlookup() function.

That being said, you might be able to use the Countif() function for your
purposes, but since you gave no indication of your data configuration,
it's
difficult to determine if it (Countif) will work.

For example, with fruit in every row of Column A,where the variety is in
the
corresponding row of Column B:

=Countif(A1:A100="apple")

If this doesn't work, post back with your data set-up.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------


"Dewayne" wrote in message
...
I have a list in an Excel worksheet that is also used as a VLOOKUP table
to
calculate values applied to various products, specifically, varietal
names
of
fruits such as apples, pears, cherries etc. Each fruit has several
names
of
its varieties listed in the table (as an example apples are listed as
Gala,
Braeburn, Fuji etc.). The variety names for each fruit are inter-mixed
with
other fruit's variety names in the table to accomplish the "sort by
ascending" requirement for a VLOOKUP table.
The result of the VLOOKUP is used to multiply (or divide) by the values
in
a
different cell to create a value in the dependent cell.
What I need to accomplish is to "sum" the values in the range of
dependent
cells for only a specific fruit i.e. apple varieties, then pear
varieties,
etc. so that I end up with total values by fruit for apples, pears,
cherries
or any other fruits on the list.
Any suggestion would be apprecited.
Thank you,
Dewayne
--
Dewayne