1. I need to do a count with several criteria, and am struggling to get it to work:

=countif(left(Rejections!K2:K5000,2),"11") < or "80" and also Rejections!D25000 = "10" or "40"

And separately:

=countif(left(Rejections!K2:K5000,2),"11") < or "80" and also Rejections!D25000 = "10" or "40" and if Rejections!M2:M5000 not ""

2. Try

=SUMPRODUCT(((Left(Rejections!K2:K5000,2)="11")+(L eft(Rejections!K2:K5000,2)="80"))*((Rejections!D25000 = "10")+(Rejections!D25000 = "40")))

3. [quote name='HansV' post='764681' date='10-Mar-2009 19:58']Try

=SUMPRODUCT(((Left(Rejections!K2:K5000,2)="11")+(L eft(Rejections!K2:K5000,2)="80"))*((Rejections!D25000 = "10")+(Rejections!D25000 = "40")))[/quote]

Thanks, perfect! Where do I add the other condition for the second one?

4. [quote name='VegasNath' post='764684' date='10-Mar-2009 21:24']Thanks, perfect! Where do I add the other condition for the second one?[/quote]
More "AND" conditions can be created by appending another multiplication.
More exclusive "OR" conditions can be created by appending an addition.
You must take great care that the parentheses are placed correctly.

If the conditions become very complicated, it might be easier to write a custom VBA function.

5. Thanks Hans. I take it that this does not work on columns?

If I try with K:K, I get #NUM, and if I extend the range to include my header, 1:5000, I get #N/A.

The problem that I have, is that the data is sorted, and moved around using VBA, after which, the range becomes distorted.

How would I go about writing this formula to the cell using vba, following the sort?

6. [quote name='VegasNath' post='764710' date='10-Mar-2009 23:20'][/quote]
You cannot use an entire column such as K:K in an array formula or array-like formula with SUMPRODUCT.

If you'd like help with VBA, please provide enough information.

7. I was thinking:

With Worksheets("Summary")
Range("J21").Formula = "=SUMPRODUCT(((LEFT(Rejections!\$K\$2:\$K\$5000,2)="11 ")+(LEFT(Rejections!\$K\$2:\$K\$5000,2)="80"))*((Rejec tions!\$D\$2:\$D\$5000 = "10")+(Rejections!\$D\$2:\$D\$5000 = "40")))"

...... more of the same

End With

But I get Expected end of statement error. ?

8. Your formula contains quotes. To include quotes within a quoted string, you must double them:

Range("J21").Formula = "=SUMPRODUCT(((LEFT(Rejections!\$K\$2:\$K\$5000,2)=""1 1"")+..."

BTW the line

With Worksheets("Summary")

doesn't do anything since you don't refer to it - you should use .Range(...) instead of Range(...)

9. Thanks.
Sorry to be a pain, but any idea what is wrong with this one?

Range("K21").Formula = "=SUMPRODUCT((((LEFT(Rejections!\$K\$2:\$K\$5000,2)="" 11"")+(LEFT(Rejections!\$K\$2:\$K\$5000,2)=""80""))*(( Rejections!\$D\$2:\$D\$5000=""10"")+(Rejections!\$D\$2:\$ D\$5000=""40"")))*((Rejections!\$M\$2:\$M\$5000<>"")))"

10. You haven't doubled the quotes within the formula at the end.