Countif help needed
in [Functions]
|
From: Jessica Krall Jessica on 11 May 2010 10:11 I am trying to use a countif statement for column A to count the number of times a District is "CAR" PLUS those CAR listings where column N (DAYS) is >499. I've tried =COUNTIF(A:A="CAR")+(N:N=">499") and =SUM((District="CAR")*(Days>499)) as an array, but neither works...help please!! Jess
From: "Bernie Deitrick" deitbe on 11 May 2010 10:17 Jessica, Close: =SUMPRODUCT((District="CAR")*(Days>499)) HTH, Bernie MS Excel MVP "Jessica Krall" <Jessica Krall(a)discussions.microsoft.com> wrote in message news:1C4594ED-8F33-4E1E-9FB8-80066C050443(a)microsoft.com... >I am trying to use a countif statement for column A to count the number of > times a District is "CAR" PLUS those CAR listings where column N (DAYS) is >>499. I've tried =COUNTIF(A:A="CAR")+(N:N=">499") and > =SUM((District="CAR")*(Days>499)) as an array, but neither works...help > please!! > > Jess
From: T. Valko on 11 May 2010 10:19 Try something like this... =SUMPRODUCT(--(A1:A10="car"),--(N1:N10>499)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Jessica Krall" <Jessica Krall(a)discussions.microsoft.com> wrote in message news:1C4594ED-8F33-4E1E-9FB8-80066C050443(a)microsoft.com... >I am trying to use a countif statement for column A to count the number of > times a District is "CAR" PLUS those CAR listings where column N (DAYS) is >>499. I've tried =COUNTIF(A:A="CAR")+(N:N=">499") and > =SUM((District="CAR")*(Days>499)) as an array, but neither works...help > please!! > > Jess
From: Dave Peterson on 11 May 2010 10:29 And if you're using xl2007, take a look at =countifs() in Excel's help. And if District and Days are names of entirecolumns, then Biff explained why it failed in xl2003 (and below). Jessica Krall wrote: > I am trying to use a countif statement for column A to count the number of > times a District is "CAR" PLUS those CAR listings where column N (DAYS) is > >>499. I've tried =COUNTIF(A:A="CAR")+(N:N=">499") and > > =SUM((District="CAR")*(Days>499)) as an array, but neither works...help > please!! > > Jess -- Dave Peterson
From: RonaldoOneNil on 11 May 2010 10:51
Use this and adjust the range accordingly =SUMPRODUCT((A1:A1000="CAR")*(N1:N1000>499)) "Jessica Krall" wrote: > I am trying to use a countif statement for column A to count the number of > times a District is "CAR" PLUS those CAR listings where column N (DAYS) is > >499. I've tried =COUNTIF(A:A="CAR")+(N:N=">499") and > =SUM((District="CAR")*(Days>499)) as an array, but neither works...help > please!! > > Jess |