Your formula only calculates the number of ROWS with a 1 in A:D and
"at least one 5" in E:H, but the OP wanted to count the CELLS with 5,
not the rows, as I read it.

To get the same result as Bernard Liengme got, and what I guess is the
OP i saying, I would remove the very last "0" in your formula.
This will only make a difference if there would ever be more than one
"5" in any row. But we can not assume the contrary, can we.
Put a 5 in G1 to see what I mean.

Lars-Åke

On Sat, 9 Aug 2008 17:36:34 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I wasn't 100% sure what 2 the OP is trying to count, so I used your posting
as my guide.

I think this single formula (requiring no helper columns) accomplishes what
the OP is attempting to do?

=SUMPRODUCT(((A1:A5=1)+(B1:B5=1)+(C1:C5=1)+(D1:D5 =1)0)*((E1:E5=5)+(F1:F5=5)+(G1:G5=5)+(H1:H5=5)0) )

Rick


"Bernard Liengme" wrote in message
...
In J1 use:
=SUMPRODUCT(--(OR(A1=1,B1=1,C1=1,D1=1)),COUNTIF(E1:H1,5))
If required use =SUM(J1:J4)

Let's see an array-formula wizard do it all with one formula and no helper
column.
Or would you like a VBA solution?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chris" wrote in message
...
I cannot seem to find an answer, so I am posting he

A B C D E F G H
1 1 5 3 4 2
2 1 4 4 3 5
3 2 3 4 4 3
4 2 3 3 3 5

What I am looking to do is create a formula that can look in columns A
through D for the numer 1. Then in those rows that have the number 1, to
count the numbers in that same row where there is a 5. So in the example
above, there are two rows with the number 1 - and if you count the cells
in
those two rows that have the number 5, it would equal 2.

I was trying to create this as one formula. Any help is appreciated.
Thanks