Cells A1:A100 may contain "ABC", "DEF" or "GHI"
Cells B1:B100 will contain a date if work on the entries in "A" have been
completed.
I wish to count the number of "ABC"s only if there is a date next to them in
column B in the same row indicating work complete.
I would then like to do the same for "DEF" and "GHI"

--
tia

Jock

Hi Jock

Try
=SUMPRODUCT(($A$1:$A$100="ABC")*(ISNUMBER($B$1:$B$100)))

Either repeat for DEF and GHI or put the values ABC etc, in cells D1:F1
and use

=SUMPRODUCT(($A$1:$A$100=D$1)*(ISNUMBER($B$1:$B$100)))
and drag across

--
Regards

Roger Govier


"Jock" <(E-Mail Removed)> wrote in message
news:F65D3515-C037-4ED4-A845-(E-Mail Removed)...
> Cells A1:A100 may contain "ABC", "DEF" or "GHI"
> Cells B1:B100 will contain a date if work on the entries in "A" have
> been
> completed.
> I wish to count the number of "ABC"s only if there is a date next to
> them in
> column B in the same row indicating work complete.
> I would then like to do the same for "DEF" and "GHI"
>
> --
> tia
>
> Jock

Thank you Roger, works a treat.
Regards,

Jock


"Roger Govier" wrote:

> Hi Jock
>
> Try
> =SUMPRODUCT(($A$1:$A$100="ABC")*(ISNUMBER($B$1:$B$100)))
>
> Either repeat for DEF and GHI or put the values ABC etc, in cells D1:F1
> and use
>
> =SUMPRODUCT(($A$1:$A$100=D$1)*(ISNUMBER($B$1:$B$100)))
> and drag across
>
> --
> Regards
>
> Roger Govier
>
>
> "Jock" <(E-Mail Removed)> wrote in message
> news:F65D3515-C037-4ED4-A845-(E-Mail Removed)...
> > Cells A1:A100 may contain "ABC", "DEF" or "GHI"
> > Cells B1:B100 will contain a date if work on the entries in "A" have
> > been
> > completed.
> > I wish to count the number of "ABC"s only if there is a date next to
> > them in
> > column B in the same row indicating work complete.
> > I would then like to do the same for "DEF" and "GHI"
> >
> > --
> > tia
> >
> > Jock
>
>
>

Or if you really want to do it with code:-

Sub merged()
searchfor = InputBox("What are we looking for?")
Dim myRange As Range
Set myRange = Range("A1:A100")
For Each c In myRange
c.Select
If c.Value = searchfor And IsDate(Selection.Offset(0, 1).Value) Then
Count = Count + 1
End If
Next
MsgBox (Count & " instances of " & searchfor)
End Sub

"Jock" wrote:

> Cells A1:A100 may contain "ABC", "DEF" or "GHI"
> Cells B1:B100 will contain a date if work on the entries in "A" have been
> completed.
> I wish to count the number of "ABC"s only if there is a date next to them in
> column B in the same row indicating work complete.
> I would then like to do the same for "DEF" and "GHI"
>
> --
> tia
>
> Jock

I'll keep that one on stand by Mike. Thanks


Jock


"Mike H" wrote:

> Or if you really want to do it with code:-
>
> Sub merged()
> searchfor = InputBox("What are we looking for?")
> Dim myRange As Range
> Set myRange = Range("A1:A100")
> For Each c In myRange
> c.Select
> If c.Value = searchfor And IsDate(Selection.Offset(0, 1).Value) Then
> Count = Count + 1
> End If
> Next
> MsgBox (Count & " instances of " & searchfor)
> End Sub
>
> "Jock" wrote:
>
> > Cells A1:A100 may contain "ABC", "DEF" or "GHI"
> > Cells B1:B100 will contain a date if work on the entries in "A" have been
> > completed.
> > I wish to count the number of "ABC"s only if there is a date next to them in
> > column B in the same row indicating work complete.
> > I would then like to do the same for "DEF" and "GHI"
> >
> > --
> > tia
> >
> > Jock