Count cells that meet 2 IF Conditions

[TheRedCardinal]

Hi all,

I want to count the number of rows for which 2 simple logical tests are met.

My data is in range A4:F114.

What I want to do is count how many times:

1. The first 7 characters in column B are "Prepare"
2. The value of column F is "On Time"

I worked out how to the first bit using an array formula:

=COUNT(IF(LEFT(B4:B114,7)="Prepare",1,""))

(I also need to know how many times just column B shows Prepare)

I then tried to expand this using an IF/AND combination:

=COUNT(IF(AND(LEFT(B4:B114,7)="Prepare",F4:F114="On Time"),1,""))

But this is returning a zero for me even though the condition is met 27 times.

So what obvious step have I missed?

Thanks!

 

[steve the fish]

Try:

=SUMPRODUCT(--(LEFT(B4:B114,7) = "prepare"),--(F4:F114 = "on time"))

 

[linkin_anytime]

Can you try this?

=COUNTIFS(B4:B114,"Prepare*",F4:F114,"On Time")

 

[TheRedCardinal]

Can you try this?

=COUNTIFS(B4:B114,"Prepare*",F4:F114,"On Time")

Wildcards! Why didn't I think of that.

Thanks

 

[TheRedCardinal]

Try:

=SUMPRODUCT(--(LEFT(B4:B114,7) = "prepare"),--(F4:F114 = "on time"))

This one works too!