need formula help please

  • Thread starter Thread starter john
  • Start date Start date
J

john

i have two cell ranges, b34:b134 and i34:i134 and i need to know for each
'b' cells that contain a specific value, how many of the 'i' cells (in the
same row) contain a 'y'.

i tried =SUM(IF(B33:B134="1",IF(J33:J134="y",IF(J33:J134="n",1,0)))) but it
counts all the values in the 'b' row that batch to a '1' and all the values
in the 'i' row that match to a 'y'. i need it to count all the '1's in the
'b' cells that have a 'y' in the 'i' cell.
'b' 'i'
34 1 y
35 2 n
36 1 n
37 2 y
38 1 y

in cell j15, i want a value of 2 (the number of 1's in the 'b' column that
have a 'y' in the 'i' column.

your help is much appreicated.
john w zerkel
 
Hi John!

Try one way:

=SUMPRODUCT((B33:B134=1)*(I33:I134="y"))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
i discovered the error in my formula. what will work is:

=SUM(IF((B34:B134=1)*(I34:I134="y"),1,0))
 
Back
Top