COUNT IF formulas

I ahve a multi columned spreadsheet that I need to pull some data from.
I cant figure out how to do this.

i.e i need to know how many cells in f have the value "open" if coulmn b has the value "door" and column e has the value "frame".

If (column b= door) and (column e= frame) how many cells in column f = door

and another would be.. where--(column b= door) and (column e= frame) and (column f =door)-then what is the total of values in column g

thanks

Originally Posted by john_learner

I ahve a multi columned spreadsheet that I need to pull some data from.
I cant figure out how to do this.

i.e i need to know how many cells in f have the value "open" if coulmn b has the value "door" and column e has the value "frame".

If (column b= door) and (column e= frame) how many cells in column f = door

and another would be.. where--(column b= door) and (column e= frame) and (column f =door)-then what is the total of values in column g

thanks

try following array (hit Ctrl+Shift+Enter)

=SUM(IF(B1:B50="door",IF(E1:E50="frame",IF(F1:F50="door",1,0),0)))

Regards.

Originally Posted by starguy

try following array (hit Ctrl+Shift+Enter)

=SUM(IF(B1:B50="door",IF(E1:E50="frame",IF(F1:F50="door",1,0),0)))

Regards.

thankyou

that works good

and If want a formula to add up the values of the cells in column j that meet
these (IF(B1:B50="door",IF(E1:E50="frame",IF(F1:F50="door",1,0),0))) conditions?

Originally Posted by john_learner

thankyou

that works good

and If want a formula to add up the values of the cells in column j that meet
these (IF(B1:B50="door",IF(E1:E50="frame",IF(F1:F50="door",1,0),0))) conditions?

try following.

=SUMPRODUCT(((B1:B50="door")*(E1:E50="frame")*(F1:F50="door")*(J1:J50)))

Regards.