Microsoft Excel 365 column totals

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

Use SUMPRODUCT():

=SUMPRODUCT(A2:A9,B2:B9) You can use following Formula to get total number of parts required to be quoted, whenever you add Price to an item. 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.