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.)


SUMIF is the normal wethod, e.g.


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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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