question about countif

[jon]

Hi
I want to count the number of "a" in column "B" which is easy
(countif(b4:b50,"a") but I also want to see what the count is when column
"A" is taken in to the equation.
In column A is the date.
So I want a count the Number of "a" in column B where the date in column A
is in the last 12 months/365 days?

Thanks

Jon

 

[Dave Peterson]

This may work for you:

=sumproduct(--(a4:a50>=date(year(today())-1,month(today()),day(today()))),
--(b4:b50="A"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

====
You may want to check the result when the current date is Feb 29.

 

[jon]

Sorted
I swapped it round to check for the "a" before the date
But it works fine.

Thanks
Jon