INDEX MATCH with two columns of text?

[NJS1982]

Hi all, hope somebody can help.
I have two columns of text for each record (risk):
- Column F is a score (Red, Amber, Green)
- Column L is a Status (To Begin, On hold, In Progress, Completed, Cancelled)

I want to put in a formula that will count the number of Reds that are NOT Completed or Cancelled (i.e. how many risks are outstanding/live and still classed as Red).

I tried mixing in a number of COUNTIF and COUNTA formulas, but it only amalgamated all results...I guess I have to do some sort of Index formula, but I can't figure it out...?

 

[Special-K99]

Try

=SUMPRODUCT((F1:F1000="Red")*(L1:L1000<>"Completed")*(L:L1000<>"Cancelled"))

 

[NJS1982]

Hi, thanks, I tried this and am getting a figure of 1, when I have 'Red' appearing in both F65 and F66, not 2?

=SUMPRODUCT((F64:F104="Red")*(L64:L104<>"Completed")*(L64:L104<>"Cancelled"))

 

[Special-K99]

L65 and L66 need to be NOT Completed and NOT Cancelled


F column being Red doesnt count on its own as you specified in your original description