# How to sum values depending on other columns (related variables) - please see example.

1. ## How to sum values depending on other columns (related variables) - please see example.

Hello,
I find this question difficult to describe in words, so I hope this will do the trick:
I have binary data in four columns: A, B, C and D. These are arranged in rows (a large number). In a fifth column, S, I want to Sum all the 1's in a given column (A, B, C or D), thereby obtaining a "column height" for each set of 1's.
The output would look something like this:

A B C D S
0 1 0 0 1
1 0 0 0 2
1 0 0 0 2
0 0 1 0 1
0 1 0 0 1
1 0 0 0 1
0 1 0 0 1
1 0 0 0 3
1 0 0 0 3
1 0 0 0 3
0 1 0 0 1
0 0 1 0 4
0 0 1 0 4
0 0 1 0 4
0 0 1 0 4

You can hopefully see better from the example than my explanation what I am trying to achieve. Columns A, B, C and D are the input, and Column S is what I am trying - and currently failing - to calculate.

Any help gratefully appreciated !

Thank you for taking the time to read this, whether or not you can help.

Rob

2. ## Re: How to sum values depending on other columns (related variables) - please see example.

As there are only 0's and 1's, can you simply (in E2 for example, dragged down) put

=SUM(A2:D2)
?
EDIT: Okay, looking at your sums, you are obviously doing something else. Cant' see it being a power of 2 thing. Exactly how are you summing?

3. ## Re: How to sum values depending on other columns (related variables) - please see example.

You say you want to Sum the columns but there are 4 columns and only 1 Summation value?

Okay, I see it now. Putting on my thinking cap. Will there always only be one 1 in any particular row?

4. ## Re: How to sum values depending on other columns (related variables) - please see example.

Originally Posted by ChemistB
As there are only 0's and 1's, can you simply (in E2 for example, dragged down) put

=SUM(A2:D2)
?
EDIT: Okay, looking at your sums, you are obviously doing something else. Cant' see it being a power of 2 thing. Exactly how are you summing?
Hi, first thank you for your time.
well, I don't think that works, if I '=sum(A2:D2) , it returns 1 for each line, whereas for example on line 2, I want a 2 returned, and on line 13, I want a 4 returned.
do you see what I'm trying to achieve?

Rob

5. ## Re: How to sum values depending on other columns (related variables) - please see example.

Originally Posted by ChemistB
You say you want to Sum the columns but there are 4 columns and only 1 Summation value?

Okay, I see it now. Putting on my thinking cap. Will there always only be one 1 in any particular row?
:-) !!
thank you ! my thinking cap is dry now.. so any help more than welcome !

6. ## Re: How to sum values depending on other columns (related variables) - please see example.

Hi Rob,
I'm not quite sure what you are trying to do.
What is the difference between the second and the sixth row concerning the calculations for the S column ?

7. ## Re: How to sum values depending on other columns (related variables) - please see example.

Originally Posted by jimcot
Hi Rob,
I'm not quite sure what you are trying to do.
What is the difference between the second and the sixth row concerning the calculations for the S column ?
Hi,

basically I'm trying to get a column that tells me how many consecutive 1's there are in either A, B, C or D.
is that any help? I realise my question is perhaps not worded as well as it should be..

Rob

8. ## Re: How to sum values depending on other columns (related variables) - please see example.

Okay, Assuming your data is in A2:D16
First, put a string of 0's in the row under your last row (i.e A17=0,B17=0,C17=0 and D17=0)
In E2

=MATCH(0,INDEX(A2:D\$17,,MATCH(1,A2:D2,0)),0)-1

In E3

=IF(MATCH(1,A3:D3,0)=MATCH(1,A2:D2,0),E2,(MATCH(0,INDEX(A3:D\$17,,MATCH(1,A3:D3,0)),0)-1))
Does that work for you?

9. ## Re: How to sum values depending on other columns (related variables) - please see example.

Originally Posted by ChemistB
Okay, Assuming your data is in A2:D16
First, put a string of 0's in the row under your last row (i.e A17=0,B17=0,C17=0 and D17=0)
In E2

=MATCH(0,INDEX(A2:D\$17,,MATCH(1,A2:D2,0)),0)-1

In E3

=IF(MATCH(1,A3:D3,0)=MATCH(1,A2:D2,0),E2,(MATCH(0,INDEX(A3:D\$17,,MATCH(1,A3:D3,0)),0)-1))
Does that work for you?
Hi,

well I've tried that but doesn't quite do it, here is the sheet output:
A B C D Excel1 Excel2 S
0 1 0 0 1 2 1
1 0 0 0 2 2 2
1 0 0 0 1 1 2
0 0 1 0 1 1 1
0 1 0 0 1 1 1
1 0 0 0 1 1 1
0 1 0 0 1 3 1
1 0 0 0 3 3 3
1 0 0 0 2 2 3
1 0 0 0 1 1 3
0 1 0 0 1 4 1
0 0 1 0 4 4 4
0 0 1 0 3 3 4
0 0 1 0 2 2 4
0 0 1 0 1 #N/A 4
0 0 0 0 #N/A #N/A

The last line is that string of 0's, and column G is the desired output.
You're way closer than I was though..

Rob

10. ## Re: How to sum values depending on other columns (related variables) - please see example.

Are columns E and F relevant? If so, what are they? See my attachment. Don't drag the formula down into the row of zeros.

Edit: No need to quote my posts back, it just clutters up the board. Thanks

11. ## Re: How to sum values depending on other columns (related variables) - please see example.

Columns E and F were just the columns including your formulae, and noted, I won't quote.. new user here so thanks for the hint !
Ok, I've opened your spreadsheet and I think I just dragged/copied the wrong way. Your sheet is exactly what I'm trying to get. I'll give it a try, and if aok post Solved.
thanks again !!!

Rob

12. ## Re: How to sum values depending on other columns (related variables) - please see example.

Perfect !

many thanks, and I'll post thread solved, thanks again !

Rob

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1