[SOLVED] Need help with countif or another count function

[[email protected]]

OK, I am trying to use countif, but with more that one criteria. Is
this possible.

I have rows that have a format like this:

Letter Amt Date

I need it to count how many rows have a certain letter between certain
dates? Does anybody know how to do this?

[Lonnie M.]

michael, Try the followning; assuming Letter is in column 'A' and Date
is in Column 'C':

Sub LetterCount()
Dim C&, X&, cnt&
Dim myLetter As String
Dim minDate, maxDate As Date
myLetter = whatever letter
minDate = whatever date
maxDate = whatever date
C = Range(Cells(2, 1), Cells(2, 1).End(xlDown)).Rows.Count
For X = 1 To C
If Cells(X + 1, 3) >= minDate Or Cells(X + 1, 3) >= maxDate Then
If Cells(X + 1, 1) = myLetter Then
cnt = cnt + 1
End If
End If
Next X
MsgBox "The letter """ & myLetter & """ appears in " & cnt & " rows."
End Sub

HTH--Lonnie M.

[Fredrik Wahlgren]

<[email protected]> wrote in message
news:[email protected]...
> OK, I am trying to use countif, but with more that one criteria. Is
> this possible.
>
> I have rows that have a format like this:
>
> Letter Amt Date
>
> I need it to count how many rows have a certain letter between certain
> dates? Does anybody know how to do this?
>

I have found an article that mentions countif with more than one criteria
here: http://www.mrexcel.com/td0128.html
I don't know how to deal with the dates, though. It must be possible to get
date as a number, right. In that case, this article will get you started.

/Fredrik

[Bob Phillips]

Worksheet functions okay?

=SUMPRODUCT(--(A1:A1000,"A"),--(C1:C1000>=--"2004-01-01"),--(C1:C1000<=--"20
04-01-20"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> OK, I am trying to use countif, but with more that one criteria. Is
> this possible.
>
> I have rows that have a format like this:
>
> Letter Amt Date
>
> I need it to count how many rows have a certain letter between certain
> dates? Does anybody know how to do this?
>

[Tom Ogilvy]

That formula isn't applicable in this case. It is for a range of values
within a single column. This problem involves criteria against two columns.
Countif doesn't work in that situation.

Bob Phillips has posted an Array formula (SumProduct) which does it.

--
Regards,
Tom Ogilvy

"Fredrik Wahlgren" <[email protected]> wrote in message
news:%[email protected]...
>
> <[email protected]> wrote in message
> news:[email protected]...
> > OK, I am trying to use countif, but with more that one criteria. Is
> > this possible.
> >
> > I have rows that have a format like this:
> >
> > Letter Amt Date
> >
> > I need it to count how many rows have a certain letter between certain
> > dates? Does anybody know how to do this?
> >
>
> I have found an article that mentions countif with more than one criteria
> here: http://www.mrexcel.com/td0128.html
> I don't know how to deal with the dates, though. It must be possible to
get
> date as a number, right. In that case, this article will get you started.
>
> /Fredrik
>
>