Product Sums

[FH1987]

Thank you for the response. I'll play around a little in the test area and see if I can figure out what went wrong.

I'll show you (and other posters) exactly the kind of problem I was having.
Below is what I wrote:

"This is my first post on this forum, I would greatly appreciate help with the following problem I have in excel 2000.

For a project at work I'm going to make a dropdown list displaying various products, how many of them we've sold, and the unit price of the respective product.

Then I'm going to multiply the number of sales with the unit price, thereby arriving at the total revenue for the product selected in the dropdown list.

Here's a generalized example:

A Column B Column C Column D Column
1
2 Product* Number of sales Unit Price Total Revenue
3 Apples 50 10 $ =sumproduct
4 Oranges 75 7 $ (B3:B5,C3:C5)
5 Bananas 83 12 $

Now here's the problem I'm having:
When I filter the list with the dropdown menu (Indicated by an asterix) to include only, say, apples. The Formula in cell D3 Still returns the product sum of all the fruits, even the ones currently not showing due to the filter.

How do I make the formula take note only of the items currently showing in the list?

I would greatly appreciate a speedy response"




When I view the text in the box where you write the post everything is nice and tidy, but when I click "submit Reply" or "preview post" everything is thrown around making the example I provided to illustrate the excel problem impossible for readers to follow.
Do you know what I am missing here?

 

[jeffreybrown]

Re: How to use this forum?

Please look at my signature where you will find an HTML maker designed by Richard Schollar specifically for MrExcel board.

For a possible answer to your question, have you looked at the subtotal function?

 

[Aladin Akyurek]

Re: How to use this forum?

Thank you for the response. I'll play around a little in the test area and see if I can figure out what went wrong.

I'll show you (and other posters) exactly the kind of problem I was having.
Below is what I wrote:

"This is my first post on this forum, I would greatly appreciate help with the following problem I have in excel 2000.

For a project at work I'm going to make a dropdown list displaying various products, how many of them we've sold, and the unit price of the respective product.

Then I'm going to multiply the number of sales with the unit price, thereby arriving at the total revenue for the product selected in the dropdown list.

Here's a generalized example:

A Column B Column C Column D Column
1
2 Product* Number of sales Unit Price Total Revenue
3 Apples 50 10 $ =sumproduct
4 Oranges 75 7 $ (B3:B5,C3:C5)
5 Bananas 83 12 $

Now here's the problem I'm having:
When I filter the list with the dropdown menu (Indicated by an asterix) to include only, say, apples. The Formula in cell D3 Still returns the product sum of all the fruits, even the ones currently not showing due to the filter.

How do I make the formula take note only of the items currently showing in the list?

I would greatly appreciate a speedy response"




When I view the text in the box where you write the post everything is nice and tidy, but when I click "submit Reply" or "preview post" everything is thrown around making the example I provided to illustrate the excel problem impossible for readers to follow.
Do you know what I am missing here?

You could have the following formula in D1 (outside the autofiltered area), not in D3...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B5,ROW(B3:B5)-ROW(B3),,1)),B3:B5,C3:C5)

 

[FH1987]

Re: How to use this forum?

Thanks you very much for the reply! It actually seems to be exactly what I was looking for!

The other questing I was having: When I provide an example of what I am doing. Like this:

A Column B Column C Column D Column
1
2 Product* Number of sales Unit Price Total Revenue
3 Apples 50 10 $ =sumproduct
4 Oranges 75 7 $ (B3:B5,C3:C5)
5 Bananas 83 12 $

Everything is nice and tidy untill I press the "Submit" button. Then it all gets thrown around and rendered impossible to follow. (As in the above example)

I'm amazed that Aladin Akyurek, despite this, was able to divine what I was going for and provide help on so short notice. That's very kind of you!

Jeffreybrown suggested an HTML maker for the post formating. The thing is, I hardly know what HTML is or how it would be of help. Do you think you could explain exactly what I'm supposed to do?

 

[jeffreybrown]

Re: How to use this forum?

If you follow this link there are instructions which should help get you going

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2