Need a formula to count values in different rows
 

I have two rows, and I want to add the total number of columns which have a 1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So here
the count would be 5.

I cant get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

One way:

in the row below enter:

A3: =--OR(A1,A2)

and then copy across to F3, then you can just:

=SUM(A3:F3)

Hope this helps.

Pete

On Apr 10, 10:34 am, ceri_m wrote:
I have two rows, and I want to add the total number of columns which have a 1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So here
the count would be 5.

I can't get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

I think this does what you want...

=SUMPRODUCT(--((A1:Z1+A2:Z2)0))

Rick


"ceri_m" wrote in message
...
I have two rows, and I want to add the total number of columns which have a
1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So
here
the count would be 5.

I cant get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

Try

=SUMPRODUCT((A3:F3=1)*(A4:F4=1))

With the ranges suitable adjusted.

Mike

"ceri_m" wrote:

I have two rows, and I want to add the total number of columns which have a 1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So here
the count would be 5.

I cant get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

If you do not want to use another row you could use this
=12-COUNTIF(A1:F2,1)
where 12 is the total number of 0's and 1's in your example

"ceri_m" wrote:

I have two rows, and I want to add the total number of columns which have a 1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So here
the count would be 5.

I cant get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

Well that's what you dont want!!

Try this instead

=COUNTIF(A3:F3,"=1")+SUMPRODUCT((A4:F4=1)*(A3:F3< 1))


Mike

"Mike H" wrote:

Try

=SUMPRODUCT((A3:F3=1)*(A4:F4=1))

With the ranges suitable adjusted.

Mike

"ceri_m" wrote:

I have two rows, and I want to add the total number of columns which have a 1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So here
the count would be 5.

I cant get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

Forget my post, it does not work

"ceri_m" wrote:

I have two rows, and I want to add the total number of columns which have a 1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So here
the count would be 5.

I cant get my head around it at all!!! Please help!

Need a formula to count values in different rows
 

Thanks for you help guys - Rick's worked best for what I was after, Mike- for
soem reason it was giving me an answer of 0 with your formula, and Pete's it
came with #value. But it's sorted now, so thank you all! :)

"Rick Rothstein (MVP - VB)" wrote:

I think this does what you want...

=SUMPRODUCT(--((A1:Z1+A2:Z2)0))

Rick


"ceri_m" wrote in message
...
I have two rows, and I want to add the total number of columns which have a
1
in them, over both rows.

1 0 0 1 1 0
1 1 0 0 1 1

So if a 1 occurs whether in one or both rows, it still counts as 1. So
here
the count would be 5.

I cant get my head around it at all!!! Please help!