cannot solve with countif function

[sanlen]

Hi,

I am trying to use countif function to count value in columnA1:A11 and the condition would be base on columnB1:B11=cellE1. I am trying with this =COUNTIF(A1:A11,B1:B11=E1).

could you please advice if i am on the right way? i also would like to solve this with sumproduct, would it work?

I have attached a worksheet along. Thanks you very much for your time and all the comments you may give me.

[Ron Coderre]

The COUNTIF function only test its criteria on one range.
Consequently, in your formula:

Please Login or Register  to view this content.

You could either test Col_A or Col_B, but not both.
Since you appear to be interested in testing Col_B for the value of E1,
try this formula:

Please Login or Register  to view this content.

Does that help?

[sanlen]

Hi,

Thanks you very much.

Yes, it's working fine now. But what if i want to count only those group that already started their work?

I have edited my attachment and add them along.

Thanks you very much for your time.

[alansidman]

Try this:
{=SUMPRODUCT((B2:B12<>"")*(C2:C12=F2))}. To get the curly brakets, you will need to select the Control + Shift + Enter keys simoultaneously.

[Ron Coderre]

No need to CTRL+SHIFT+ENTER...The SUMPRODUCT function handles the situation nicely.