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?
Gregg Helsel replied to Martin Schmidt on 30-Nov-12 05:59 PM
Test Billing.zip

Would like to do away with column G "Ext Amt".
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.

Martin Schmidt replied to Gregg Helsel on 30-Nov-12 10:00 PM
Paste =SUMPRODUCT(($A$2:$A$32=A35)*1,$E$2:$E$32,$F$2:$F$32) in G35:G38