Date ranges in functions

[Ken]

Hi

I wish to utilise the COUNTIF and SUMIF functions to
evaluate data whose "range" is a lis of dates.

eg: I have a column which has a large number of entries
specified by their creation date in the
format "dd,mmmm,yyyy". How can I count the number of
examples that were in say June 2003?

Will the fix also work for a SUMIF on another column
("sum_range")using the same date "range"?

Thanks Ken

 

[Arvi Laanemets]

Hi


Let's have the 1st of month you want to count/sum in cell A1 (you can format
the cell as 'mmmm yyyy'), dates in A5:A1000 and values in B5:B1000

Count:
=SUMPRODUCT((A5:A1000>=A1)*(A5:A1000<=DATE(YEAR(A1),MONTH(A1)+1,0)))

Sum:
=SUMPRODUCT((A5:A1000>=A1)*(A5:A1000<=DATE(YEAR(A1),MONTH(A1)+1,0))*(B5:B100
0))


Arvi Laanemets

 

[Myrna Larson]

Assuming the dates are in A1:A100

=COUNTIF(A1:A100,">="&DATE(2003,6,1)) - COUNTIF(A1:A100,">="&DATE(2003,7,1))

The same approach will work with SUMIF.

=SUMIF(A1:A100,">="&DATE(2003,6,1),B1:B100) - SUMIF(A1:A100,">="&DATE(2003,7,1),B1:B100)