Excel sum from column based on another column

Question

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!

Answer 1

Excel 2010 (and many versions previous¹) has SUMIF - you can use the result from this, and multiply it by the variable (this works out, because ax+bx+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

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

Answer 2

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.

Answer 3

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.