I did say I didn't fully understand what the OP was asking.<g Had he given
more than one 5 in one of the rows that met his condition, it would have
been easier to figure out what he wanted. To me, it was like trying to
figure out if he wanted to add or multiply numbers by giving us an example
with two 2s and a result of 4... there is no way to tell with that example
set if 2+2 or 2*2 was the correct answer.
Rick
"Lars-Åke Aspelin" wrote in message
...
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:D 5=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