Use SUMPRODUCT:

For date of 1st July 2006:

=SUMPRODUCT(--(A1:A5=DATE(2006,7,1)),--(B1:B52))

or month of July:

=SUMPRODUCT(--(Month(A1:A5)=7),--(B1:B52))

HTH

"dinadvani via OfficeKB.com" wrote:

Hi,

I am in trouble and your help.....

I have an excel sheet that contains data for June and July. The data cannot
be sorted and contains one day may be more than once.

For instance
After using countif
Dates Values Dates
No. of times date gets repeated
6/5/06 0
6/5/06 2
6/4/06 1
6/4/06 1
6/5/06 2
6/8/06 4
6/8/06 2
7/8/06 4
7/8/06 1
6/8/06 1
Using countif, I am able to count the no. of times these dates are repeating
(in our example - 6/5/06 -2, 6/8/06 -1)

You can see that there are some values besides different date, now what I
need is to calulate how many times the value is above 2 for a particular day
i.e 6/8/06 - 1. If day wise is not possible, please let me know if monthwise
wld be possible.

Please Help ASAP

Thanks,
Dinesh Advani

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1