Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

share|improve this answer
It works! Thank you so much! – overcomplicating things Oct 29 '12 at 0:23
+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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .