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 a rather strange formula I'd like to do. What I want is for a cell, lets say D1, to get a value that is a sum of all valid numbers in the range B1 to B100, where the only condition to be a valid number is that for each cell looked at (ie B1, B2, B3) it looks to the left at A1, A2, A3, and sees if it contains "true" or "false". If it's true, then it adds that to the sum, if not, it skips over it.

I thought there was a way you could do this with like vlookup or whatnot. I know I can do it with a macro, no problem, but I'm not 100% certain if there's a way to do this with a formula instead (which would automatically update the value in D1 if I change something in column A or B. That automatic update is the big thing I need.)

share|improve this question
add comment

1 Answer

SUMIF is the normal wethod, e.g.

=SUMIF(A1:A100,TRUE,B1:B100)

You can have compilcations with TRUE/FALSE, though - the above works if TRUE/FALSE are Boolean values rather than text values

share|improve this answer
    
It works! Thank you so much! –  overcomplicating things Oct 29 '12 at 0:23
1  
+1 - Just complementing with something that might be useful. The SUMIF fuction is the best way to sum the column (which is what you need) but if the values in Column A are True or False, you can use a simple multiplication of A and B columns (instead of IF(A1=True;B1;0) in your formulas). (A1*B1) will give you 0 if A1 = False and B1 if A1 = True. (False = 0 and True = 1 for multiplication). So, for example to sum the column, you can sum (A1*B1) of each row adding a column (hidden or not) with A*B and sum it. –  laurent Oct 29 '12 at 0:39
    
@overcomplicatingthings Just a reminder that a green tick is usually appreciated for an answer that works. –  pnuts Oct 30 '12 at 4:35
add comment

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.