# Microsoft Excel - How do I get a subtotal using SUMPRODUCT, grouped by a value in the first column

*Asked By ***Gregg Helsel** on 30-Nov-12 04:21 PM

Using SUMPRODUCT, I can multiply the units (column E) by the unit prices(column F) to get the total value of several rows of items. I want to subtotal certain rows based upon a value in column A. A work around is to multiply columns E & F with the result in column G and then using VLOOKUP to reference the value in column A and the amounts in column G. I'm trying to limit the number of columns. How do I do it?

**Martin Schmidt** replied to **Gregg Helsel** on 30-Nov-12 05:23 PM

If I understand your question right you have a list of units (Column E) and unit prices (Column F) that have a category attached to them and you want to sum up the total of each category (Column A).

I think you could do something like this

Next to each category (Column B)?

=SUMPRODUCT(($D:$D=A1)*1,$E:$E,$F:$F)

With column D being the column with your category.

If this is not right can you post an example of your data?

**Donald Ross** replied to **Gregg Helsel** on 30-Nov-12 09:13 PM

Gregg,

Looking at your post you have only used 7 colums and the one colum you are asking to 'do away' with is the only colum that contains any formulas for totaling your bid quanty's

this sheet is pretty straight forward.