If & Count Statement
 

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

If & Count Statement
 

Try some helper columns

=COUNTIF(A2:D2,1)
=COUNTIF(E2:H2,5)

That should get you started.
--
HTH,
Barb Reinhardt



"Chris" wrote:

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

If & Count Statement
 

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

If & Count Statement
 

Alternative to my SUMPRODUCT
=COUNTIF(A1:D1,1)*COUNTIF(E1:H1,5)
--
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

If & Count Statement
 

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

If & Count Statement
 

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

If & Count Statement
 

Assuming you want to count *every* instance of 5 when 1 appears on the same
row:

=SUMPRODUCT((MMULT(--(A1:D4=1),{1;1;1;1})0)*(E1:H4=5))

Note: this is limited to a range size that is no more than 5461 rows.
A1:H5461 will work. A1:H5462 will not.

--
Biff
Microsoft Excel MVP


"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

If & Count Statement
 

On Sat, 9 Aug 2008 18:11:33 -0300, "Bernard Liengme"
wrote:

Alternative to my SUMPRODUCT
=COUNTIF(A1:D1,1)*COUNTIF(E1:H1,5)

This formula only works under the assumption that there will never be
more than one 1 on the same row in columns A:D.
I therefore think that your first formula, with the OR, is more safe.

Lars-Åke

If & Count Statement
 

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