0

Creating a spreadsheet to track quotes.

I have multiple columns of information.

One has the item name, Next is the quantity required for that item, next column has the price of each part.

Each time that I add a price to an item, I want the spreadsheet to total the number of parts (items x quantity) required to quote.

So if there are 30 rows of items, the quantity of each can range from 1 to 5.

There is a total at the bottom for the quantity of items to quote. I want it to update each time a price is put into the price column and multiply the individual quantity that corresponds to that price and giving a total at the bottom.

"Required Spare Qty"    "New Quoted Price for Each
1                        $13,815 
2                       0
1                        $17,338 
2                       0
1                        $2,533 
2                       0
2                       0
1                       0
0

Use SUMPRODUCT():

=SUMPRODUCT(A2:A9,B2:B9)

enter image description here

0

You can use following Formula to get total number of parts required to be quoted, whenever you add Price to an item.

enter image description here

How it works:

I'm assuming that you have Data in Range A2:C8.

  • To get Unique part's name write the Array formula in Cell A12finish with Ctrl+Shift+enter & fill down as many rows you feel (Keep some extra rows to get New Part's Name).

    {=IFERROR(INDEX($A$3:$A$8, MATCH(0, IF($C$3:$C$8>0, COUNTIF($A$11:$A11, $A$3:$A$8), ""), 0)),"")}
    
  • To get Total numbers of Part's write this Formula in Cell B12 and fill down (Keep some extra rows to get new total).

    =IF(SUMIF(A3:A8,A12,B3:B8)=0,"",SUMIF(A3:A8,A12,B3:B8))
    
  • Additionally, you can use following Formula to get Price total of the parts need to be quoted in cell C12 & fill it down (also keep some extra rows).

     =IF(SUMIF(A3:A8,A12,C3:C8)=0,"",SUMIF(A3:A8,A12,C3:C8))
    

Note:

  • Situation 1 shows summary of two parts Fan & Glass for which price has been entered, and Situation 2 show 3 items since has new entry Disc's Price.
  • Adjust cell references in the Formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.