Excel sum from column based on another column

I have two columns. The values in the first one are either blank or have a 1. The values in the second one is a number. I also have a variable field.

At the bottom of each column, I'd like to have a "total" field, which checks if there is a value (of 1) in the first column, and if there is, adds this up from the value of the second column (on the same row) and multiplies it by the variable.

for example:

``````variable 10
name1 name2  counter
1      2
1            3
1     1      3
1            4

totals
100    50
``````

since name1 has 3 1's in it's column, it takes each value from the counter column, and multiplies it by the variable, and outputs the total

I'm sorry if this has been asked, I've tried searching but I have a hard time understanding the excel syntaxes.

Thanks!

-

Excel 2010 (and many versions previous1) has `SUMIF` - you can use the result from this, and multiply it by the variable (this works out, because a*x+b*x+c*x = (a+b+c)*x)

so, the formula would be:

``````=SUMIF(A3:A50,1,\$C3:\$C50)*\$B\$1
``````

you could copy this over to column B (where Excel would change it to `=SUMIF(B3:B50,1,\$C3:\$C50)*\$B\$1` ) and get the result for the 2nd column

1 Thanks @Barry, I evidently forgot it was in many many versions

-
`SUMIF` is available in older versions of Excel too - `SUMIFS` (with an "S" on the end) was only introduced in Excel 2007 – barry houdini Aug 30 '12 at 19:35
@barryhoudini, well - oops. seems I've forgotten some of my excel history :) – SeanC Aug 30 '12 at 21:26
Thanks, this worked great! – jsmars Aug 30 '12 at 22:34

You don't need helper columns for this, what you need are Excel Tables and the trusty `SUMPRODUCT()` formula.

It's easier to have this in front of you, so I created an Excel file that does what you want. When you open that file, you'll see that I've created a Table out of your data (highlight and press Ctrl+t) and then added a `Totals Row` (right click on table, select Table > Totals Row) with a custom formula that allows me to use the `SUMPRODUCT()` formula multiplied by the variable you created.

That formula, for future reference, is `=SUMPRODUCT(--([name1]=1), [counter])*\$B\$1` where \$B\$1 is the Variable cell you mentioned. This will take all the rows in the `name1` column that equal 1, add up their corresponding `counter` values, and multiply by the `variable`.

Let me know if you have any questions.

-
This was also a good solution, though for my current document I'd rather not have tables. I'll keep this in mind for the future tho, thanks! – jsmars Aug 30 '12 at 22:35

The easiest way to do this is to use helper columns (a common Excel tactic).

1. Add two columns, call them SubTotal1 and SubTotal2.
2. SubTotal1 = Name1 * Counter, SubTotal2 = Name2 * Counter (the blanks in the names evaluate to zero).
3. Add two new Total cells at the bottom of your new Total Columns.
4. Total1 = sum(Subtotal1 column) * Variable, Total2 = sum (Subtotal2 column) * Variable.

-