Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 some data in columns, several binary variables:

| A | B | C |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 0 | 0 | 1 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 1 | 1 | 0 |

and then another variable with three values:

| X |
| 1 |
| 0 |
|-1 |
| 1 |
| 0 |
| 1 |

What I want to do is to write a lookup per column A-C... that, when it finds a 1 variable, adds the corresponding number from the same row in Column X to an ongoing count.

Now, the twist is that I want to maintain two different counts, one for all of the 1s in Column X that correspond to 1s in columns A-C... and another for all the -1s that similarly correspond.

Is there a simple way to do this? I assume it involves vlookup.

share|improve this question
I changed the data to layout in columns, please edit if I missed anything. – Brad Patton Apr 24 '13 at 2:32
Also I've read the question a number of times and still unclear on what you are trying to accomplish. Any different way to explain the logic? – Brad Patton Apr 24 '13 at 2:34
sure, I basically only want to do counts in column X for corresponding rows in, say, A, where the value in A is 1. So, if a value in a given row for column A is 1, I want to then look over at column X in the same row and if the value in column X is a 1 or a -1 I want to increment a positive and negative counter, if that makes sense. – fox Apr 24 '13 at 2:44
So your expected result column is: 2, 0, -1, 1, 0, 2? If not, can you show your expected result column(s)? – mcalex Apr 24 '13 at 3:13
I'd prefer to have a pair of results rather than a column, in the case of A, positive would be 1 and negative would be 0; in the case of C, positive would be 1 and negative would be 1. Both columns essentially work as a mask over X. – fox Apr 24 '13 at 4:54

If I am reading the question and comments right I think just using 'IF's and 'AND's will do what you want. Something like

= IF(AND(A1=1,X=1),"Positive ++", IF(AND(A1=1,X=-1),"Negative ++", ""))

Replace Positive and Negative ++ with the actual formula to increment your counters.

share|improve this answer
ok, what would a sample formula to increment counters look like? – fox Apr 24 '13 at 3:50

Okay, so I had some help but got this working

Essentially I used a DSUM lookup, with the columns (inc headers) as the database field, the individual column headers as the field (i.e. "A"), and a part of the sheet that looked like this


X     X
> 1   < 1

selected as criteria. Now I have two cells for positive and negative counts per column.

share|improve this answer

Your Answer


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.