Countif type thingy...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
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
 
Thank you Roger, works a treat.
Regards,

Jock


Roger Govier said:
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
Click to expand...
 
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
 
Back
Top