The formulas were not working consistently because some of the cells that
appeared to contain blanks actually had spaces in them and therefore were not
seen as blank by excel. Once I cleared all empty cells of potential spaces,
etc. the =SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<"")) worked like a
champ.

Thanks for your help. It's thrilling when you finally figure out what was
causing the problem.

"Dave Peterson" wrote:

=SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869<""))
or
=SUMPRODUCT((E4:E869="1247.24")*(isnumber(AC4:AC86 9)))

If you have any text in ac4:ac869, then this won't give you the results you
want.

If you have any numeric entries in ac4:ac869 that are not dates, then this won't
work.


Civette wrote:

=date() only works for a specific date. Unfortunately, I'm not interested in
a specific date, I am interested in all records that meet 2 criteria: column
A =1247.24 and Column E contains a date. If you have any ideas, I'm open to
suggestions.

."Dave Peterson" wrote:

It doesn't look like you've tried Biff's first suggestion.

Use =date() when entering the date--not just a string.

Civette wrote:

I originally tried using
=SUMPRODUCT((E4:E869="1247.24")*AC4:AC869="**/**/**")) but had no success.
So, I tried a specific number since I knew that date met the criteria I was
looking for...and, still I received a "0" .

"T. Valko" wrote:

a date (any date) appears in the second column.

Hmmm....

You say "any date" but yet you're testing for a specific date criteria!

Try it like this:

=SUMPRODUCT(--(E4:E869=1247.24),--(AC4:AC869=DATE(2006,1,1)))

Better to use cells to hold the criteria then you won't get messed up with
quotation marks:

A1 = 1247.24
B1 = 1/1/2006

=SUMPRODUCT(--(E4:E869=A1),--(AC4:AC869=B1))


--
Biff
Microsoft Excel MVP


"Civette" wrote in message
...
I have a spreadsheet containing thousands of records (each row is a
different
record) and 20 different data columns.

I want to count the number of records when the a specific number is in one
column and a date (any date) appears in the second column. The column
containing the dates may also contain blank cells, since this column is
not
filled in until some action triggers the need to put in a date. I've
tried
the following formula and get "0". Can someone help?

=SUMPRODUCT((E4:E869="1247.24")*(AC4:AC869="01/01/2006"))




--

Dave Peterson


--

Dave Peterson