# 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 `A12`finish 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.