SUMIF/COUNTIF w/ Dates

  • Thread starter Thread starter Smeds
  • Start date Start date
S

Smeds

I am trying to count and sum certain fields based on
date. However the formula wizard appears that my dates
are not recognized as numbers, therefore I cannot do a
=sumif(A:A<today()) or similar countif functions. The
dates are numbers, I can format them either as dates or
general numbers but it doesn't work either way. Is there
a workaround?
 
Smeds,

It sounds as if you have text fields, not dates. Here is one suggestion if
this is so. This counts them

=SUMPRODUCT(--(A1:A1000=TEXT(TODAY(),"dd-mmm-yy")))

The dd-mmm=yy format should be changed to whatever format your 'dates' are
in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Smeds!

Here's the syntax to use:

=SUMIF(A:A,"<"&TODAY(),B:B)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Try a macro

Function myCountIf(range)
myCountIf = 0
For Each cell In range
If IsEmpty(cell) Then
'prevents counting empty cells
ElseIf cell < Date Then
myCountIf = myCountIf + 1
End If
Next
End Function

you might want want to add another parameter for the date in case yo
don't want the function to always compare the dates to toda
 
Hi Smeds!

Pleased to help. I must admit to not really knowing why we *have* to
use that peculiar syntax for the criteria; other than that it won't
work if you don't.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top