need to create a formula

  • Thread starter Thread starter Roy Salinas
  • Start date Start date
R

Roy Salinas

I need to create a formula that calculates the number of
times a name shows in column A and has a date in column C
and a blank in column D. I am woring on a single
worksheet. I have tried using this formula =COUNTA
(($A$2:$A$18="Roy"),($C$2:$C$18>0),($D$2:$D$18=" ")) , all
I get is a 0 when I can see more than 1. If I change to
COUNTIF, I get an error.
 
first put this function in a REGULAR (not sheet or ThisWorkbook) module
Function IsDate(Var As Variant) As Boolean'Chip Pearson
IsDate = VBA.IsDate(Var)
End Function
then try
=sumproduct((rngA="myname")*(isdate(rngB))*(isblank(rngC))
=====
Well that won't work so use this where h1 is a date
=SUMPRODUCT((A1:A10="myname")*(B1:B10>H1)*(C1:C10=""))
 
=SUMPRODUCT(($A$27:$A$40="Roy")*($C$27:$C$40>0)*($D$27:$D$40=""))

will work if you have only dates and blanks in Column C, but not if you
have any thing else there that will evaluate to >0 (e.g., any text).

Alan Beban
 
Good idea, Don. Another approach is TRIM. Anyway, seems that Roy already had the solution,
he just needed an exact problem to use it with.

Best wishes Harald
Excel MVP

Followup to newsgroup only please.
 
as in this one I use in a checkbook program
=SUMPRODUCT((ChecksC=TRIM(B17))*ChecksD)
 
Back
Top