COUNTIF based on 2 criteria

[Pauljj]

I am using this formula

=SUMPRODUCT(--(E10:I10="TRAINING"),--(E10:I10="LEAVE"))


To count how many times the word LEAVE and TRAINING appear .

I have the word training in E10 and LEAVE in F10 but it counts zero????


Have I missed something ??

 

[Aladin Akyurek]

You have probably stray spaces around the entries in the relevant ranges. Try to remove them. The TrimAll macro and ASAP Utilities are the best tools to solve that problem.

 

[Pauljj]

It made no difference ??????

 

[maytas]

Hi,
Try =SUMPRODUCT(--(E10:I10="TRAINING")+(E10:I10="LEAVE"))
or =SUMPRODUCT(--(E10:I10="TRAINING")--(E10:I10="LEAVE"))

 

[Pauljj]

Many thanks, removing the comma did the trick, thank you

 

[Aladin Akyurek]

Many thanks, removing the comma did the trick, thank you

Comma as list separator is used on US systems. It's semi-colon [i.e., ;] in Europe.

If E10 = TRAINING and F10 = LEAVE, are you wanting to count 1 or 2?