Countif type thingy...
 

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

Countif type thingy...
 

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$10 0)))
and drag across

--
Regards

Roger Govier


"Jock" wrote in message
...
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

Countif type thingy...
 

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

Countif type thingy...
 

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$10 0)))
and drag across

--
Regards

Roger Govier


"Jock" wrote in message
...
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

Countif type thingy...
 

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