countif text?

S

shakey1181

Struggling with a formula to count cells if corresponding cells are
populated. I;ve got the below so far:

=IF(B5:B45="S",COUNTA(I5:I45))

so i want to count all the 'I' cells only when the corresponding 'B' cell
contains an S. Column I contains text. Once I have done this, I also need to
sum another column once these two criteria are met. so something like:

=if(b5:b45="S",counta(i5:i45)),sum(g5:b45)

obviously both are wrong, so any hot tips from you guys/girls would be
gratefully recieved.
 
D

David Biddulph

I'm not quite sure what criteria you are looking for in column I, but you
could try
=SUMPRODUCT(--(B5:B45="S"),--(I5:I45<>""),G5:G45)
 
D

David Biddulph

Another option, depending on the criteria you're looking for in column I,
would be =SUMPRODUCT(--(B5:B45="S"),--ISTEXT(I5:I45),G5:G45)
 
Top