Sum total stock value for any part in Microsoft Excel





Data in Cells A1:C10

 

A B C
1 Parts Quantity Price
2 A880Q 345 15.00
3 B901V 205 12.00
4 C666R 257 13.00
5 A880Q 413 18.00
6 C666R 413 18.00
7 A880Q 517 20.00
8 B901V 621 22.00
9 C666R 673 23.00
10 A880Q 725 24.00

Part # in cell E2:   A880Q

Formula in cell F2:{=SUM(IF(Parts=E2,Price*Quantity,0))}

Result:               40,349

 

List of Define Names
Parts =Sheet1!$A$2:$A$10
Price =Sheet1!$C$2:$C$10
Quantity =Sheet1!$B$2:$B$10

Please note:
The formula is an Array Formula, after entering the formula in the cell, press F2 and than press Ctrl+Shift+Enter.

Screenshot // Sum total stock value for any part in Microsoft Excel



4 thoughts on “Sum total stock value for any part in Microsoft Excel

  1. A superb little trick that I had forgotten how to do – one of those useful tips that saves loads of file space and makes it easier to understand the calculation.

  2. “I totally follow the instructions above but my results are
    not as same as showed. The Part do not sum for all item, but sum only for the certain row.
    What shold I do, I am assigned to prepare a worksheet which have the result of sum of each product like this.
    Your advices would be so much meaning…”

  3. “Are you using the data in the example above?

    If so, please can you post back with EXACTLY what you see in the formula bar in Excel (should start with an open curly bracket { as above), and what result you are getting. “

Leave a Reply

Your email address will not be published. Required fields are marked *


6 + three =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>