IF Statement using COUNTIF criteria

[umarraza]

Hi,

I have drawn the table which I am currently working on. I need the formula in B2. I can use the same formula I used in B1 but for B2 I only want it to count the number of No if there is a date entered in D7.

I think this can be done by using an IF statement but not sure what the formula will be.

Can someone please help.

Thank you in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fault Resolved[/TD]
[TD]=COUNTIF(E4:E5000,"Yes")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Fault Not Resolved[/TD]
[TD]?????[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Total[/TD]
[TD]=sum(B2+B3)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Date Fault Noticed[/TD]
[TD]Fault Resolved[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.01.15[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2.02.15[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

IF(D4:D5000<>"",COUNTIF(E4:E5000,"No"),"")

Not tested, but give it a try.

 

[dreid1011]

It's wrong if you haven't tried it yet. I will adjust.

 

[umarraza]

It's wrong if you haven't tried it yet. I will adjust.

Hey,

I did try it but it did not work. Thank you that would be very helpful.

 

[dreid1011]

For the life of me, my brain is not working right now to figure out a single formula for this, so this is what I have.

You will have to add a helper column to your sheet and add this formula from row 4-5000 in column:
=IF(AND($D$4:$D$5000<>"",$E$4:$E$5000="No"),1,0)

Then add this formula in B3 for the total:
=SUM($F$4:$F$5000)

Replace F with whichever column you use for the helper.

If this will not work for you, then I'm sure someone will come up with a single formula in time.

 

[dreid1011]

Ok, here is a single formula to put in cell B3:

=COUNTIFS($D$4:$D$5000,"<>",$E$4:$E$5000,"No")

I was trying to type the criteria for the blank cell wrong. Btw, this will count it if anything is entered into column D, even if it's not a date.

 

[umarraza]

Ok, here is a single formula to put in cell B3:

=COUNTIFS($D$4:$D$5000,"<>",$E$4:$E$5000,"No")

I was trying to type the criteria for the blank cell wrong. Btw, this will count it if anything is entered into column D, even if it's not a date.

Thank you so so so much. Really appreciate it! It works

 

[dreid1011]

You're quite welcome.