Results 1 to 12 of 12
Thread: Help with formula

20090310, 14:32 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 ""
Help please.........

20090310, 14:58 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Try
=SUMPRODUCT(((Left(Rejections!K2:K5000,2)="11")+(L eft(Rejections!K2:K5000,2)="80"))*((Rejections!D25000 = "10")+(Rejections!D25000 = "40")))

20090310, 15:24 #3
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='764681' date='10Mar2009 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?

20090310, 15:27 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
[quote name='VegasNath' post='764684' date='10Mar2009 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.

20090310, 17:20 #5
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20090310, 17:34 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
[quote name='VegasNath' post='764710' date='10Mar2009 23:20'][/quote]
You cannot use an entire column such as K:K in an array formula or arraylike formula with SUMPRODUCT.
If you'd like help with VBA, please provide enough information.

20090310, 17:40 #7
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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. ?

20090310, 18:06 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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(...)

20090310, 18:36 #9
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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<>"")))"

20090310, 18:42 #10
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts

20090310, 18:43 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
You haven't doubled the quotes within the formula at the end.

20090310, 18:43 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about:
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<>"""")) )"
Steve