Column B has specific words in it (ie Tools, Cookbook, PO)
Column C has a % in it (ie 50%, 100%, 75%)
Column D-J have number in them (ie 24.3, 62, 2.50)

I have about 90 rows of this data

I'm trying to create a table underneath this list that will have the
following rows:

Tools
Cookbook
PO

What I'd like the formula to do is say if Col B = Tools then take Col C %
and * by Col D. if Col B = Cookbook then take Col C % and * by Col D. etc.


Can anyone help?

=SUMPRODUCT(--(B1:B90=B92),C1:C90,D1:D90)

B92 = Tools
You probably want to make the arrays absolute references and then have the
list of categories extending down from B92
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"KC" wrote:

Column B has specific words in it (ie Tools, Cookbook, PO)
Column C has a % in it (ie 50%, 100%, 75%)
Column D-J have number in them (ie 24.3, 62, 2.50)

I have about 90 rows of this data

I'm trying to create a table underneath this list that will have the
following rows:

Tools
Cookbook
PO

What I'd like the formula to do is say if Col B = Tools then take Col C %
and * by Col D. if Col B = Cookbook then take Col C % and * by Col D. etc.


Can anyone help?

What R U tring to say [B=C%]*D ?

"KC" wrote:

Column B has specific words in it (ie Tools, Cookbook, PO)
Column C has a % in it (ie 50%, 100%, 75%)
Column D-J have number in them (ie 24.3, 62, 2.50)

I have about 90 rows of this data

I'm trying to create a table underneath this list that will have the
following rows:

Tools
Cookbook
PO

What I'd like the formula to do is say if Col B = Tools then take Col C %
and * by Col D. if Col B = Cookbook then take Col C % and * by Col D. etc.


Can anyone help?

Hi,

try this.


Tools =SUMPRODUCT((A1:A90=A100)*(B1:B90*C1:C90))
Cookbook
PO
Where the word 'Tools is in A100

Mike

"KC" wrote:

Column B has specific words in it (ie Tools, Cookbook, PO)
Column C has a % in it (ie 50%, 100%, 75%)
Column D-J have number in them (ie 24.3, 62, 2.50)

I have about 90 rows of this data

I'm trying to create a table underneath this list that will have the
following rows:

Tools
Cookbook
PO

What I'd like the formula to do is say if Col B = Tools then take Col C %
and * by Col D. if Col B = Cookbook then take Col C % and * by Col D. etc.


Can anyone help?

oops,

wrong columns

=SUMPRODUCT((B1:B90=A100)*(C1:C90*D1:D90))

Mike

"Mike H" wrote:

Hi,

try this.


Tools =SUMPRODUCT((A1:A90=A100)*(B1:B90*C1:C90))
Cookbook
PO
Where the word 'Tools is in A100

Mike

"KC" wrote:

Column B has specific words in it (ie Tools, Cookbook, PO)
Column C has a % in it (ie 50%, 100%, 75%)
Column D-J have number in them (ie 24.3, 62, 2.50)

I have about 90 rows of this data

I'm trying to create a table underneath this list that will have the
following rows:

Tools
Cookbook
PO

What I'd like the formula to do is say if Col B = Tools then take Col C %
and * by Col D. if Col B = Cookbook then take Col C % and * by Col D. etc.


Can anyone help?