If & Count Statement

[Chris]

I cannot seem to find an answer, so I am posting here:

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

 

[Barb Reinhardt]

Try some helper columns

=COUNTIF(A22,1)
=COUNTIF(E2:H2,5)

That should get you started.

 

[Bernard Liengme]

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 Liengme]

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

 

[Rick Rothstein \(MVP - VB\)]

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)+(D15=1)>0)*((E1:E5=5)+(F1:F5=5)+(G1:G5=5)+(H1:H5=5)>0))

Rick

 

[Lars-Åke Aspelin]

Your formula only calculates the number of ROWS with a 1 in A 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

 

[T. Valko]

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

=SUMPRODUCT((MMULT(--(A14=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.

 

[Lars-Åke Aspelin]

Alternative to my SUMPRODUCT
=COUNTIF(A11,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.
I therefore think that your first formula, with the OR, is more safe.

Lars-Åke

 

[Rick Rothstein \(MVP - VB\)]

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