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 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
1  
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
1  
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

Criteria

X     X
> 1   < 1

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

share|improve this answer

You must log in to answer this question.

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