Need help with formula

[Tim]

Hello Friends

ColumnE . ColumnM. ColumnQ ColumnR
Values Dates


4 200 5-Mar-04 300(Mar) 2(Mar)
5 100 3-Mar-04 150(Feb) 1(Feb)
6 150 10-Feb-04 210(Jan) 3(Jan)
7 50 25-Jan-04
8 70 14-Jan-04
9 90 5-Jan-04


In the above example in column E from cell E4 are values
corresponding to dates in column M. The number of the
values and dates
will vary every month (one month they can be 15,next
9,next 17 etc.)
I need the formulas for columnQ and columnR. Formula in
cell Q4 should find and sum values for every month BASED
ON THE DATES IN COLUMN M. Formula in cell R4 should
calculate the number of values for every month BASED ON
THE DATES IN COLUMN M.

Any help is highly appreciated.

Tim

 

[Frank Kabel]

Hi
try for Q4
=SUMPRODUCT(--(MONTH(M4:M100)=3),E4:E100)

for R4 use
=SUMPRODUCT(--(MONTH(M4:M100)=3))

replace '3' with your month number

 

[Guest]

Works as a charm
Thank you Frank!!!