# excel: how to leverage vlookup with count

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 `1`s in Column X that correspond to `1`s in columns A-C... and another for all the `-1`s that similarly correspond.

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

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

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

-