If & Count Statement

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
Try some helper columns

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

That should get you started.
 
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
 
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
 
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
 
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.
 
Alternative to my SUMPRODUCT
=COUNTIF(A1:D1,1)*COUNTIF(E1:H1,5)
Click to expand...

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
 
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
 
Back
Top