Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
    
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 :) –  Sean Cheshire 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.

share|improve this answer
    
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.

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.