software

Hi,

I have two worksheets, first one with quot;namequot;, quot;product categoryquot;, and
quot;productquot;, second one with quot;productquot; (sames as the first sheet product),
quot;salesquot;, quot;costquot;.

How can I sum up the quot;salesquot; and quot;costquot; by the quot;product categoryquot; with the
least effort. (VB is not allowed
--
Jason
Wanna be an Excel Expert

On the first sheet (the one with name - column A, product category - column
B, and product - column C) place the following formula in column D to get
sales.

=SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$B$2:$B$4)
Where Sheet 5 is where you have your other information (Product - column a,
Sales - column b, and cost - column c).

The below formula will you get you cost.
=SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$C$2:$C$4)

Hope this helps.

Bill Horton

quot;Jason_Shanghaiquot; wrote:

gt; Hi,
gt;
gt; I have two worksheets, first one with quot;namequot;, quot;product categoryquot;, and
gt; quot;productquot;, second one with quot;productquot; (sames as the first sheet product),
gt; quot;salesquot;, quot;costquot;.
gt;
gt; How can I sum up the quot;salesquot; and quot;costquot; by the quot;product categoryquot; with the
gt; least effort. (VB is not allowed
gt; --
gt; Jason
gt; Wanna be an Excel Expert

Bill,

You are the MAN! I have been working on this till last nite, (it is 4 am
here in Shanghai). You just shot it by such a short formua. Thanks so much.
BTW, can you tell me something about the sumproudct, can't find much from the
Office Help. And what does quot;(--quot; mean?
--
Jason
Wanna be an Excel Expertquot;William Hortonquot; wrote:

gt; On the first sheet (the one with name - column A, product category - column
gt; B, and product - column C) place the following formula in column D to get
gt; sales.
gt;
gt; =SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$B$2:$B$4)
gt; Where Sheet 5 is where you have your other information (Product - column a,
gt; Sales - column b, and cost - column c).
gt;
gt; The below formula will you get you cost.
gt; =SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$C$2:$C$4)
gt;
gt; Hope this helps.
gt;
gt; Bill Horton
gt;
gt; quot;Jason_Shanghaiquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; I have two worksheets, first one with quot;namequot;, quot;product categoryquot;, and
gt; gt; quot;productquot;, second one with quot;productquot; (sames as the first sheet product),
gt; gt; quot;salesquot;, quot;costquot;.
gt; gt;
gt; gt; How can I sum up the quot;salesquot; and quot;costquot; by the quot;product categoryquot; with the
gt; gt; least effort. (VB is not allowed
gt; gt; --
gt; gt; Jason
gt; gt; Wanna be an Excel Expert

Bill,

You are the MAN. You simple formula just help me out from the issue I have
been working on since yesterday evening (it is already 4am here in shanghai).
Thanks so much!

BTW, can you tell me something about the sumproduct? Can't find much from
the Office Help. And what is the quot;(--quot; after the function?

Jason
Wanna be an Excel Expertquot;William Hortonquot; wrote:

gt; On the first sheet (the one with name - column A, product category - column
gt; B, and product - column C) place the following formula in column D to get
gt; sales.
gt;
gt; =SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$B$2:$B$4)
gt; Where Sheet 5 is where you have your other information (Product - column a,
gt; Sales - column b, and cost - column c).
gt;
gt; The below formula will you get you cost.
gt; =SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$C$2:$C$4)
gt;
gt; Hope this helps.
gt;
gt; Bill Horton
gt;
gt; quot;Jason_Shanghaiquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; I have two worksheets, first one with quot;namequot;, quot;product categoryquot;, and
gt; gt; quot;productquot;, second one with quot;productquot; (sames as the first sheet product),
gt; gt; quot;salesquot;, quot;costquot;.
gt; gt;
gt; gt; How can I sum up the quot;salesquot; and quot;costquot; by the quot;product categoryquot; with the
gt; gt; least effort. (VB is not allowed
gt; gt; --
gt; gt; Jason
gt; gt; Wanna be an Excel Expert

No problem. glad to help. The quot;(--quot; turns Excels logical values True and
False into 1's and 0's respectively. The 1's and 0's get multiplied by their
corresponding results and then everything gets added. All the items that had
0's do not show up because 0 multiplied by anything = 0.

try the following web-site:
www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks,
Bill Horton

quot;Jason_Shanghaiquot; wrote:

gt; Bill,
gt;
gt; You are the MAN! I have been working on this till last nite, (it is 4 am
gt; here in Shanghai). You just shot it by such a short formua. Thanks so much.
gt; BTW, can you tell me something about the sumproudct, can't find much from the
gt; Office Help. And what does quot;(--quot; mean?
gt; --
gt; Jason
gt; Wanna be an Excel Expert
gt;
gt;
gt; quot;William Hortonquot; wrote:
gt;
gt; gt; On the first sheet (the one with name - column A, product category - column
gt; gt; B, and product - column C) place the following formula in column D to get
gt; gt; sales.
gt; gt;
gt; gt; =SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$B$2:$B$4)
gt; gt; Where Sheet 5 is where you have your other information (Product - column a,
gt; gt; Sales - column b, and cost - column c).
gt; gt;
gt; gt; The below formula will you get you cost.
gt; gt; =SUMPRODUCT(--(Sheet5!$A$2:$A$4=$C2),Sheet5!$C$2:$C$4)
gt; gt;
gt; gt; Hope this helps.
gt; gt;
gt; gt; Bill Horton
gt; gt;
gt; gt; quot;Jason_Shanghaiquot; wrote:
gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; I have two worksheets, first one with quot;namequot;, quot;product categoryquot;, and
gt; gt; gt; quot;productquot;, second one with quot;productquot; (sames as the first sheet product),
gt; gt; gt; quot;salesquot;, quot;costquot;.
gt; gt; gt;
gt; gt; gt; How can I sum up the quot;salesquot; and quot;costquot; by the quot;product categoryquot; with the
gt; gt; gt; least effort. (VB is not allowed
gt; gt; gt; --
gt; gt; gt; Jason
gt; gt; gt; Wanna be an Excel Expert