countif text?

[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.

 

[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)

 

[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)