need to create a formula

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.
 
D

Don Guillett

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=""))
 
A

Alan Beban

=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
 
H

Harald Staff

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.
 
D

Don Guillett

as in this one I use in a checkbook program
=SUMPRODUCT((ChecksC=TRIM(B17))*ChecksD)
 
Top