COUNTIF In This Case?

  • Thread starter Thread starter salvatore
  • Start date Start date
S

salvatore

I have a column with dates in it, and Id like to have a total at the
bottom that sums the number of occurances where the date is less than
today's date. I was thinking something like this:

=COUNTIF((E7:E136),"<=TODAY()")

But all that does is evaluate to 1 or 0 based on the logical operator
and whether or not there are *any* values that match the criteria.
Meaning, if there's any that meet this, the result is one, and if not,
the result is zero. What im looking for is a result of the sum of the
total that meet the criteria; say, ten or fifteen or however many dates
there are in the array less than today's date.

Am I overcomplicating this?
Any help is met with much love and appreciation.
 
I have a column with dates in it, and Id like to have a total at the
bottom that sums the number of occurances where the date is less than
today's date. I was thinking something like this:

=COUNTIF((E7:E136),"<=TODAY()")

But all that does is evaluate to 1 or 0 based on the logical operator
and whether or not there are *any* values that match the criteria.
Meaning, if there's any that meet this, the result is one, and if not,
the result is zero. What im looking for is a result of the sum of the
total that meet the criteria; say, ten or fifteen or however many dates
there are in the array less than today's date.

Am I overcomplicating this?
Any help is met with much love and appreciation.
Click to expand...

You are very close. Try:

=COUNTIF((E7:E136),"<=" & TODAY())


--ron
 
Frank Kabel helped me on something similar (see my "can
I simplify" question, 7 threads further up the page).

Try
=SUMPRODUCT(--(E7:E136<TODAY()))

The double unary '-' is important.
More SUMPRODUCT help at
www.xldynamic.com/source/xld.SUMPRODUCT.html
(see example 1, about 70% of the way through)
-----Original Message-----
Click to expand...
I have a column with dates in it, and Id like to have a
total at the bottom that sums the number of occurances
where the date is less than today's date. I was thinking
something like this:

=COUNTIF((E7:E136),"<=TODAY()")

But all that does is evaluate to 1 or 0 based on the
logical operator and whether or not there are *any* values
that match the criteria.
Meaning, if there's any that meet this, the result is one,
and if not, the result is zero. What im looking for is a
result of the sum of the total that meet the criteria;
say, ten or fifteen or however many dates there are in the
array less than today's date.

Am I overcomplicating this?
Any help is met with much love and appreciation.

...salvatore
 
Try
=SUMPRODUCT(--(E7:E136<TODAY()))
Click to expand...

It appears the COUNTIF function skips blank cells, and the SUMPRODUCT
includes them in the count.
In my case, I'll need them skipped, as its ok for a cell to be blank and
not be counted.

Thanks for the additional resource though; I wasnt aware of this function.
 
In this case, COUNTIF is the way to go, but you can skip blanks in a
SUMPRODUCT formula by adding one term:

=SUMPRODUCT(--(E7:E136<TODAY()),--(E7:E136<>""))
 
Back
Top