Hi,

I have two worksheets, first one with "name", "product category", and
"product", second one with "product" (sames as the first sheet product),
"sales", "cost".

How can I sum up the "sales" and "cost" by the "product category" 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

"Jason_Shanghai" wrote:

Hi,

I have two worksheets, first one with "name", "product category", and
"product", second one with "product" (sames as the first sheet product),
"sales", "cost".

How can I sum up the "sales" and "cost" by the "product category" with the
least effort. (VB is not allowed :)
--
Jason
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 "(--" mean?
--
Jason
Wanna be an Excel Expert


"William Horton" wrote:

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

"Jason_Shanghai" wrote:

Hi,

I have two worksheets, first one with "name", "product category", and
"product", second one with "product" (sames as the first sheet product),
"sales", "cost".

How can I sum up the "sales" and "cost" by the "product category" with the
least effort. (VB is not allowed :)
--
Jason
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 "(--" after the function?

Jason
Wanna be an Excel Expert


"William Horton" wrote:

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

"Jason_Shanghai" wrote:

Hi,

I have two worksheets, first one with "name", "product category", and
"product", second one with "product" (sames as the first sheet product),
"sales", "cost".

How can I sum up the "sales" and "cost" by the "product category" with the
least effort. (VB is not allowed :)
--
Jason
Wanna be an Excel Expert

No problem. glad to help. The "(--" 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:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks,
Bill Horton

"Jason_Shanghai" wrote:

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 "(--" mean?
--
Jason
Wanna be an Excel Expert


"William Horton" wrote:

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

"Jason_Shanghai" wrote:

Hi,

I have two worksheets, first one with "name", "product category", and
"product", second one with "product" (sames as the first sheet product),
"sales", "cost".

How can I sum up the "sales" and "cost" by the "product category" with the
least effort. (VB is not allowed :)
--
Jason
Wanna be an Excel Expert