Count number of items by month
 

I'm sure my answer has already been posted, but I've tried searching and
nothing seems to fit.

I want to count the number of cells in a range where the value of column A
is "1", and the date in column B is within the month of August. I will then
in a separate cell need to average the numbers in column C, where A=1 and
B=sometime in August.

I've tried sumproduct and countif's for the first one but nothing seems to
work. And for the second one, getting the average to work only during those
conditions is stumping me.

Thanks in advance for all help!

Hi!

For the count:

=SUMPRODUCT(--(A1:A10=1),--(MONTH(B1:B10)=8))

For the average:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF((A1:A10=1)*(MONTH(B1:B10)=8),C1:C10))

Biff

"Christine" wrote in message
...
I'm sure my answer has already been posted, but I've tried searching and
nothing seems to fit.

I want to count the number of cells in a range where the value of column A
is "1", and the date in column B is within the month of August. I will
then
in a separate cell need to average the numbers in column C, where A=1 and
B=sometime in August.

I've tried sumproduct and countif's for the first one but nothing seems to
work. And for the second one, getting the average to work only during
those
conditions is stumping me.

Thanks in advance for all help!

what formulas did you use?
try
=sumproduct(--(A1:A1000=1),--(month(B1:b1000)=8))
and
=sumproduct(--(A1:A1000=1),--(month(B1:b1000)=8),C1:1000)/sumproduct(--(A1:A1000=1),--(month(B1:b1000)=8))

the other thing that often happens is that the date is not really a date but
text,
If these don't work, try changing the format of the data fields and see if
they relflect the change.

If they don't try inserting =datevalue(B1) [or some cell which has one of
the dates] and try changing the format in this cell.
if this works change the recomended formulas to
....month(datevalue(B1:B1000))...
for all of the month sections.


"Christine" wrote:

I'm sure my answer has already been posted, but I've tried searching and
nothing seems to fit.

I want to count the number of cells in a range where the value of column A
is "1", and the date in column B is within the month of August. I will then
in a separate cell need to average the numbers in column C, where A=1 and
B=sometime in August.

I've tried sumproduct and countif's for the first one but nothing seems to
work. And for the second one, getting the average to work only during those
conditions is stumping me.

Thanks in advance for all help!