Need a formula to count values in different rows

[ceri_m]

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!

 

[Pete_UK]

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

 

[Rick Rothstein \(MVP - VB\)]

I think this does what you want...

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

Rick

 

[Mike H]

Try

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

With the ranges suitable adjusted.

Mike

 

[Dennis]

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

 

[Mike H]

Well that's what you dont want!!

Try this instead

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


Mike

 

[Dennis]

Forget my post, it does not work

 

[ceri_m]

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!